I have a query in my Asp.net project, which create two table and then insert amount of date into it, when there are afew data so there is no problem, it is my query :
protected void btnLottryInformationSave_Click(object sender, EventArgs e)
{
try
{
int rowCount;
var finalTempTableName = FindTableName();
var selecterServicesList = cblServices.Items.Cast<ListItem>().Where(x => x.Selected).ToList();
var selectedServices = selecterServicesList.Aggregate(string.Empty, (current, service) => current + ("'" + service.Value + "',")).TrimEnd(',');
var tempTableName = "Temp_" + finalTempTableName;
var drawQuery = " if (OBJECT_ID('Temp.dbo." + tempTableName + "') is not null) \ndrop table Temp.dbo." + tempTableName + ";\n";
drawQuery += " create table Temp.dbo." + tempTableName + "(Number char(11) primary key,RepCount int);\n";
drawQuery += " if (OBJECT_ID('Temp.dbo." + finalTempTableName + "') is not null) \ndrop table Temp.dbo." + finalTempTableName + ";\n";
drawQuery += " create table Temp.dbo." + finalTempTableName + "(Id int identity(1,1) primary key,Number char(11));\n";
drawQuery += " select org.orig \n" +
" into #MoOrig \n" +
" from VSReceiveSend.dbo.OrigPrices org \n" +
" INNER JOIN VSReceiveSend.dbo.Services s on (s.Orig = org.Orig) \n" +
" where s.Code in (" + selectedServices + ") and org.MTPrice <> 0 \n ";
if (drpLotteryType.SelectedValue == "1" || drpLotteryType.SelectedValue == "2")
{
drawQuery += " INSERT INTO Temp.dbo." + tempTableName + " (Number,RepCount)\n" +
" select number," + (drpLotteryType.SelectedValue == "1" ? "1" : "sum(c) ") +
" from (\n" +
" SELECT Number,COUNT(*) c \n" +
" FROM VSReceiveSend.dbo.ReceivedSMS with(nolock)\n" +
" INNER JOIN VSReceiveSend.dbo.#MoOrig org ON (org.Orig = ReceivedSMS.Orig) \n " +
" WHERE Date BETWEEN '" + dtpStartDate.Text + "' AND '" + dtpEndDate.Text + "' \n" +
" GROUP BY Number\n" +
" union all \n " +
" SELECT Number,COUNT(*) c \n" +
" FROM VSReceiveSend.dbo.SentSMS with(nolock) \n" +
" INNER JOIN VSReceiveSend.dbo.OrigPrices org ON (org.Orig = SentSMS.Orig AND org.MTPrice <> 0 ) \n " +
" WHERE SentDate BETWEEN '" + dtpStartDate.Text + "' AND '" + dtpEndDate.Text + "' AND SentSMS.ServiceCode in (" + selectedServices + ") \n" +
" GROUP BY Number \n" +
" ) DerivedTable \n" +
" group by number \n" +
((drpLotteryType.SelectedValue == "2" && txtMinAnswerCount.Text != "") ? "HAVING sum(c) >= " + txtMinAnswerCount.Text : "") + ";\n";
}
else if (drpLotteryType.SelectedValue == "3")
{
var pointCount = int.Parse(txtMinCustomerPoint.Text);
drawQuery += " INSERT INTO Temp.dbo." + tempTableName + " ( Number,RepCount)\n" +
" SELECT Number,SUM(Point) / " + txtDivPoints.Text + " RepCount " +
" FROM VsReceiveSend.dbo.SubscriberMatchHistory with(nolock) \n" +
" where ServiceCode in (" + selectedServices + ") and SUBSTRING(DateTime,1,10) BETWEEN '" + dtpStartDate.Text + "' AND '" + dtpEndDate.Text + "' \n" +
" GROUP BY Number\n" +
" HAVING SUM(Point) >= " + pointCount + ";\n";
}
drawQuery += " with DrawInfo (Number,RepCount) as \n" +
"( select Number,1 \n" +
" from temp.dbo." + tempTableName + "\n" +
" union all \n" +
" select DrawInfo.Number,DrawInfo.RepCount + 1 \n" +
" from Temp.dbo." + tempTableName + "\n" +
" inner join DrawInfo on (DrawInfo.Number = Temp.dbo." + tempTableName + ".Number and DrawInfo.RepCount + 1 <= " + tempTableName + ".RepCount) \n" +
")\n" +
" Insert into Temp.dbo." + finalTempTableName + "(Number) \n" +
" select DrawInfo.Number \n" +
" from DrawInfo";
if (!checkBoxOrderInformation.Checked)
drawQuery += " ORDER BY ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) \n";
else
drawQuery += " ORDER BY Number \n";
drawQuery += " option (maxrecursion 0);\n " +
" select count(*) from Temp.dbo." + finalTempTableName;
using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["VasunReportConnectionString"].ConnectionString))
{
var command = new SqlCommand(drawQuery, connection) { CommandTimeout = 999999999 };
connection.Open();
drawFieldSet.Visible = true;
rowCount = (int)command.ExecuteScalar();
txtNumberOfRow.Text = rowCount.ToString(CultureInfo.InvariantCulture);
}
using (var dbs = new ReceiveSendEntitiesV5())
{
var matchDrawHistories = new MatchDrawHistory()
{
DrawStartDate = dtpStartDate.Text,
DrawEndDate = dtpEndDate.Text,
DrawTime = DateTime.Now.Hour.ToString() + ":" + DateTime.Now.Minute.ToString() + ":" + DateTime.Now.Second.ToString(),
RowNumber = rowCount,
ServiceCode = cblServices.SelectedValue,
TableName = finalTempTableName
};
dbs.MatchDrawHistories.Add(matchDrawHistories);
dbs.SaveChanges();
}
}
catch (Exception exp)
{
ErrorLabel.Text = "Exception :" + exp.Message;
}
}
but when there were a lot of data, my code execute until : txtNumberOfRow.Text = rowCount.ToString(CultureInfo.InvariantCulture);
so these codes does not execute :
using (var dbs = new ReceiveSendEntitiesV5())
{
var matchDrawHistories = new MatchDrawHistory()
{
DrawStartDate = dtpStartDate.Text,
DrawEndDate = dtpEndDate.Text,
DrawTime = DateTime.Now.Hour.ToString() + ":" + DateTime.Now.Minute.ToString() + ":" + DateTime.Now.Second.ToString(),
RowNumber = rowCount,
ServiceCode = cblServices.SelectedValue,
TableName = finalTempTableName
};
dbs.MatchDrawHistories.Add(matchDrawHistories);
dbs.SaveChanges();
}
how it is possible!!! what is the problem???
