Connecting and using database

Question

How to connect SQL Server Management Studio database table and use it in a C# program using code?

Ankit Sharma · Answer

Hi Nirmal.
There are two ways to connect to SQL server, Connected and Disconnected. Given below is an example showing how to bind to a gridview using of both these methods.
You can use any one of them in your project and alter the code as per your need.
public   void  BindDataDisConnectedModel()
{
//  Dataset and DataAdapter is used in Disconnected model
string  connectionString =  "Put your connectionString here"
SqlConnection connection =  new  SqlConnection(connectionString);
string  dbQuery =  "select * from tblstudent" ;
try
{
SqlCommand sqlCmd =  new  SqlCommand(dbQuery, connection);
connection.Open();
SqlDataAdapter sda =  new  SqlDataAdapter(sqlCmd);
DataSet dataset =  new  DataSet();
sda.Fill(dataset);
connection.Close();
GridViewDisConnected.DataSource = dataset.Tables[0];
GridViewDisConnected.DataBind();
}
catch  (SqlException ex)
{
string  msg =  "Fetch Error:" ;
msg += ex.Message;
throw   new  Exception(msg);
}
finally
{
connection.Close();
}
}
public   void  BindDataConnectedModel()
{
//  DataReader is used in Connected model
string connectionString =  "Put your connectionString here"
SqlConnection connection =  new  SqlConnection(connectionString);
string  sqlQuery =  "select * from student" ;
try
{
SqlCommand cmd =  new  SqlCommand(sqlQuery, connection);
connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
List  lstStudent =  new  List ();
while  (reader.Read())
{
Student student =  new  Student();
student.Name = reader[ "Name" ].ToString();
student.Age = Convert.ToInt32(reader[ "Age" ].ToString());
student.Roll_Num = Convert.ToInt32(reader[ "Roll_Num" ].ToString());
student.city = reader[ "City" ].ToString();
lstStudent.Add(student);
}
reader.Close();
GridViewConnected.DataSource = lstStudent;
GridViewConnected.DataBind();
}
catch  (SqlException ex)
{
string  msg =  "Fetch Error:" ;
msg += ex.Message;
throw   new  Exception(msg);
}
finally
{
connection.Close();
}
}
Hope this helps

Bharathi Raja · Answer

open the Server Explore[ctrl+alt+s] Data connection Click to add the new connection Dropdown Select Your Server name And Database name Connction add to F4 click to propert window Connection string copy to your code EX: SqlConnection con = new SqlConnection(@"Data Source=@@@@@@@@@@@@@@@;Initial Catalog=KTest;Integrated Security=True");

Suresh Kumar · Answer

string  connetionString =  null ;   SqlConnection connection;   SqlCommand command;   string  sql =  null ;   connetionString =  "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password" ;   sql =  "Your SQL Statemnt Here" ;connection =  new  SqlConnection(connetionString);   try     {   connection.Open();   command =  new  SqlCommand(sql, connection);   command.ExecuteNonQuery();   command.Dispose();   connection.Close();   MessageBox.Show ( " ExecuteNonQuery in SqlCommand executed !!" );   }   catch  (Exception ex)   {   MessageBox.Show( "Can not open connection ! " );   }

Manas Mohapatra · Answer

Follow below links that may help you:   http://www.c-sharpcorner.com/article/how-to-connect-sql-database-in-asp-net-using-c-sharp-and-insert-and-view-the-data-usi/    http://o7planning.org/en/10511/connecting-to-sql-server-database-using-csharp   https://www.dotnetperls.com/sqlclient

Mageshwaran R · Answer

Hi, Can you check with the below link: http://csharp.net-informations.com/ado.net/csharp-connection-string.htm