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

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

Connecting and using database

Answers (3)