C# .NET - Retrieve data from MS SQL Server 2000, fill a DataSet and populate to a DataGridView (C# 2008)

Asked By Aldo Liaks on 24-Jul-08 09:31 AM

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

waseem kaleem replied to Aldo Liaks on 02-Jul-12 01:35 AM
Hi
In

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

}

}


AS you want to display data in datagridview why you use  sqlComm.ExecuteNonQuery(); instead of sqlComm.ExecuteReader();

You use stored procedure but you were not setting command type as strored procedure

It may be solve your problem