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"];

}