Thursday, September 3, 2009

Retrieving Data from SQLServer using Data Set in C#

  • Assume that you have

    • SQLServer named as “My-PC
    • Database Named as “TestDB
    • UserID for SQL server as “Melick
    • Password for SQL server as “PassMelick”
String ConStr="";
ConStr="Data Source=My-PC;Initial Catalog=TestDB;Persist Security Info=True;"+
"User ID=Melick;Password=PassMelick";
SqlConnection sc = new SqlConnection(ConStr);
 
SqlCommand scmd = new SqlCommand();
scmd.CommandText = "SELECT * From Table1";
scmd.CommandType = CommandType.Text;
scmd.Connection = sc;
 
DataSet ds =new DataSet();
SqlDataAdapter da = new SqlDataAdapter(scmd);
da.Fill(ds);
 
string Name="";
int Age=0;
 
foreach (DataRow dtrow in ds.Tables["Table1"].Rows)
{
Name=dtrow["Name"].ToString();
Age=(int)dtrow["Age"];
} 

In here manually writing a connection string is difficult. therefore I always prefer to use SqlConnectionStringBuilder Class to generate the Connection String.


SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder();
sb.UserID = "Melick";
sb.Password = "PassMelick";
sb.InitialCatalog = "TestDB";
sb.DataSource = "My-PC";
 
SqlConnection sc = new SqlConnection(sb.ConnectionString);
 
SqlCommand scmd = new SqlCommand();
scmd.CommandText = "SELECT * From Table1;SELECT * From Table1";
scmd.CommandType = CommandType.Text;
scmd.Connection = sc;
 
DataSet ds =new DataSet();
SqlDataAdapter da = new SqlDataAdapter(scmd);
da.Fill(ds);
 
string Name = "";
int Age = 0;
 
foreach (DataRow dtrow in ds.Tables["Table1"].Rows)
{
// Table Having two columns Name and Age
Name =dtrow["Name"].ToString();
Age = (int)dtrow["Age"];
}
Post a Comment