Hi guys,
Note: I am working under "MS SQL Server 2000 "and "C# in Visual Studio 2008".
I need to retrieve data from MS SQL Server 2000 and populate to a DataGridView (C# 2008).
The query used to retrieve data from SQL Server looks like:
USE SCHE
if object_id('AuxTable20080722123030') is not null exec('DROP TABLE ' + 'AuxTable20080722123030')
SELECT DISTINCT
Accounts.AccountKey AS 'Accounts.AccountKey',
Accounts.FullName AS 'Accounts.FullName',
Accounts.Filter AS 'Accounts.Filter',
Accounts.SortGroup AS 'Accounts.SortGroup'
INTO AuxTable20080722123030
FROM
ACCOUNTS AS Accounts
WHERE
Accounts.SORTGROUP Between '0' AND '379'
AND Accounts.SORTGROUP Not Between '100' AND '150';
GO
ALTER TABLE AuxTable20080722123030
ADD
[TotalCosts] real;
GO
UPDATE AuxTable20080722123030
SET
[TotalCosts] = 10
SELECT DISTINCT
[Accounts.AccountKey],
[TotalCosts]
FROM AuxTable20080722123030
I need to run the query below using C# code.
I found no way to run it from code as a single batch, so after dealing with it (based on post's suggestions), I worked out two different possibilities.
I hope this will be useful for other people.
The first one is Creating and Running a Stored Procedure.
The second one is Running the query batch by batch (multi – batch).
Thanks for the help!
Aldo.
Creating and Running a Stored Procedure:
// Form Constructor
public Report()
{
InitializeComponent();
CreateSP();
RunSP();
}
// Method
#region Methods to Create and run Stored Procedures
private void CreateAndRunSP()
{
string conn = @"server=x;uid=y;pwd=z;database=xyz";
createSP_Aldo01 = String.Format(createSP_Aldo01, "AuxTable20080722123030");
try
{
using (SqlConnection myConnection = new SqlConnection(conn))
{
using (SqlCommand sqlComm = new SqlCommand())
{
myConnection.Open();
sqlComm.Connection = myConnection;
sqlComm.CommandText = "USE SCHE";
sqlComm.ExecuteNonQuery();
sqlComm.CommandText = "IF object_id('Aldo01') is not null drop procedure Aldo01 ";
sqlComm.ExecuteNonQuery();
sqlComm.CommandText = createSP_Aldo01;
sqlComm.ExecuteNonQuery();
sqlComm.CommandText = "EXEC Aldo01 '0', '379', '100', '150' ";
using (SqlDataAdapter da = new SqlDataAdapter(sqlComm))
{
DataSet ds = new DataSet();
da.Fill(ds, "Report");
dgvReport.DataSource = ds.Tables[0];
myConnection.Close();
}
}
}
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex.Message, "Connection error", MessageBoxButtons.OK, MessageBoxIcon.Error);
Application.Exit(); // finish the program
}
}
// Method
private void CreateSP()
{
string conn = @"server=x;uid=y;pwd=z;database=xyz";
createSP_Aldo01 = String.Format(createSP_Aldo01, "AuxTable20080722123030");
try
{
using (SqlConnection myConnection = new SqlConnection(conn))
{
using (SqlCommand sqlComm = new SqlCommand())
{
myConnection.Open();
sqlComm.Connection = myConnection;
sqlComm.CommandText = "USE SCHE";
sqlComm.ExecuteNonQuery();
sqlComm.CommandText = "IF object_id('Aldo01') is not null drop procedure Aldo01 ";
sqlComm.ExecuteNonQuery();
sqlComm.CommandText = createSP_Aldo01;
sqlComm.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex.Message, "Connection error", MessageBoxButtons.OK, MessageBoxIcon.Error);
Application.Exit(); // finish the program
}
}
private void RunSP()
{
string conn = @"server=x;uid=y;pwd=z;database=xyz";
try
{
using (SqlConnection myConnection = new SqlConnection(conn))
{
using (SqlCommand sqlComm = new SqlCommand())
{
myConnection.Open();
sqlComm.Connection = myConnection;
sqlComm.CommandText = "USE SCHE";
sqlComm.ExecuteNonQuery();
sqlComm.CommandText = "EXEC Aldo01 '0', '379', '100', '150' ";
using (SqlDataAdapter da = new SqlDataAdapter(sqlComm))
{
DataSet ds = new DataSet();
da.Fill(ds, "Report");
dgvReport.DataSource = ds.Tables[0];
myConnection.Close();
}
}
}
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex.Message, "Connection error", MessageBoxButtons.OK, MessageBoxIcon.Error);
Application.Exit(); // finish the program
}
}
#endregion
// SQL String declaration.
#region Create Stored Procedure in MS SQL Server
string createSP_Aldo01 = ""
+ @" CREATE PROCEDURE Aldo01 "
+ @" @AccSGBtw_Start varchar(25), "
+ @" @AccSGBtw_End varchar(25), "
+ @" @AccSGNotBtw_Start varchar(25), "
+ @" @AccSGNotBtw_End varchar(25) "
+ @" AS "
+ @" if object_id('{0}') is not null exec('DROP TABLE ' + '{0}') "
+ @" SELECT DISTINCT "
+ @" Accounts.AccountKey AS 'Accounts.AccountKey', "
+ @" Accounts.FullName AS 'Accounts.FullName', "
+ @" Accounts.Filter AS 'Accounts.Filter', "
+ @" Accounts.SortGroup AS 'Accounts.SortGroup' "
+ @" INTO {0} "
+ @" FROM "
+ @" ACCOUNTS AS Accounts "
+ @" WHERE "
+ @" Accounts.SORTGROUP Between @AccSGBtw_Start AND @AccSGBtw_End "
+ @" AND Accounts.SORTGROUP Not Between @AccSGNotBtw_Start AND @AccSGNotBtw_End "
+ @" exec ('ALTER TABLE ' + '{0}' + ' ADD [TotalCosts] real ;') "
+ @" exec ('UPDATE ' + '{0}' + ' SET [TotalCosts] = 10;') "
+ @" exec ('SELECT DISTINCT [Accounts.AccountKey], [TotalCosts] FROM ' + '{0}' + ' ;') "
+ @"";
#endregion
Running the query batch by batch (multi – batch):
// Form Constructor
public Report()
{
InitializeComponent();
RunningQueryBatchByBatch();
}
// Method
private void RunningQueryBatchByBatch()
{
string conn = @"server=x;uid=y;pwd=z;database=xyz";
cmmdString02 = String.Format(cmmdString02, "AuxTable20080722123030");
cmmdString03 = String.Format(cmmdString03, "AuxTable20080722123030");
cmmdString04 = String.Format(cmmdString04, "AuxTable20080722123030");
cmmdString05 = String.Format(cmmdString05, "AuxTable20080722123030");
cmmdString06 = String.Format(cmmdString06, "AuxTable20080722123030");
try
{
using (SqlConnection myConnection = new SqlConnection(conn))
{
using (SqlCommand sqlComm = new SqlCommand())
{
myConnection.Open();
sqlComm.Connection = myConnection;
sqlComm.CommandText = cmmdString01; sqlComm.ExecuteNonQuery();
sqlComm.CommandText = cmmdString02; sqlComm.ExecuteNonQuery();
sqlComm.CommandText = cmmdString03; sqlComm.ExecuteNonQuery();
sqlComm.CommandText = cmmdString04; sqlComm.ExecuteNonQuery();
sqlComm.CommandText = cmmdString05; sqlComm.ExecuteNonQuery();
sqlComm.CommandText = cmmdString06; sqlComm.ExecuteNonQuery();
using (SqlDataAdapter da = new SqlDataAdapter(sqlComm))
{
DataSet ds = new DataSet();
da.Fill(ds, "Report");
dgvReport.DataSource = ds.Tables[0];
}
sqlComm.CommandText = cmmdString02; sqlComm.ExecuteNonQuery();
myConnection.Close();
}
}
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex.Message, "Connection error", MessageBoxButtons.OK, MessageBoxIcon.Error);
Application.Exit(); // finish the program
}
}
// SQL String declaration.
#region Running query batch by batch.
string cmmdString01 = " USE SCHE ";
string cmmdString02 = " if object_id('{0}') is not null exec('DROP TABLE ' + '{0}') ";
string cmmdString03 = ""
+ @" SELECT DISTINCT "
+ @" Accounts.AccountKey AS 'Accounts.AccountKey', "
+ @" Accounts.FullName AS 'Accounts.FullName', "
+ @" Accounts.Filter AS 'Accounts.Filter', "
+ @" Accounts.SortGroup AS 'Accounts.SortGroup' "
+ @" INTO {0} "
+ @" FROM "
+ @" ACCOUNTS AS Accounts "
+ @" WHERE "
+ @" Accounts.SORTGROUP Between '0' AND '379' "
+ @" AND Accounts.SORTGROUP Not Between '100' AND '150' "
+ @" ; ";
string cmmdString04 = ""
+ @" ALTER TABLE {0} "
+ @" ADD "
+ @" [TotalCosts] real "
+ @" ; ";
string cmmdString05 = ""
+ @" UPDATE {0} "
+ @" SET "
+ @" [TotalCosts] = 10 ";
string cmmdString06 = ""
+ @" SELECT DISTINCT "
+ @" [Accounts.AccountKey], "
+ @" [TotalCosts] "
+ @" FROM {0} "
+ "";
#endregion |