In this article, I am going to demonstrate how to access SQL server through C#.
Adding Namespaces
using System.Data.SqlClient; // For
the database connections and objects.
Inserting & Retrieving data using CommandText
public void
CommandTextConnectionTest()
{
try
{
string ConnectionString = "ConnectionString";
SqlConnection sqlConnection = new SqlConnection();
SqlCommand sqlCommand = new SqlCommand();
sqlConnection.ConnectionString = ConnectionString;//
sqlConnection.Open();
sqlCommand.Connection =
sqlConnection;
//Insert Data
//The ExecuteNonQuery method is ideal for when
you are inserting data
sqlCommand.CommandType = CommandType.Text;
sqlCommand.CommandText = "INSERT INTO
Student values('" + "StudentID"
+ "','" + "StudentName"
+ "','" + "Address"
+ "','" + "Tpnum"
+ "')";
if (sqlCommand.ExecuteNonQuery() > 0)
{
MessageBox.Show("Successfully Inserted");
}
//Retrive Data
//SqlDataReader is a fast way to read table
data
sqlCommand.CommandText = "select * from
student where name='" + "StudentID"
+ "'";
sqlCommand.CommandType = CommandType.Text;
SqlDataReader sqlDataReader =
sqlCommand.ExecuteReader();
while (sqlDataReader.Read())
{
student.StudentID = sqlDataReader["StudentID"].ToString();// Assign StudentID to student class
student.StudentName = Convert.ToInt32(sqlDataReader["StudentName"]);
}
sqlConnection.Close();
}
catch (Exception)
{
MessageBox.Show("Error");
}
}
Inserting data using Stored procedure
public void
StoredProcedureConnectionTest()
{
try
{
string ConnectionString = "ConnectionString";
SqlConnection
sqlConnection = new SqlConnection();
SqlTransaction sqlTransaction;
SqlCommand sqlCommand = new SqlCommand();
sqlConnection.Open();
sqlCommand.Connection = sqlConnection;
sqlTransaction =
sqlConnection.BeginTransaction();
{
sqlCommand.Connection = sqlTransaction.Connection;
sqlCommand.Transaction = sqlTransaction;
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = "SP_SPNAME";
//Adding Parameters
sqlCommand.Parameters.AddWithValue("@Parameters1",
"Parameters1 value");
sqlCommand.Parameters.AddWithValue("@Parameters2",
"@Parameters2 value");
//Adding Return Parameter
sqlCommand.Parameters.Add("@ReturnParameter",
SqlDbType.VarChar, 50).Direction = ParameterDirection.Output;
sqlCommand.ExecuteNonQuery();
string reurnValue =
(sqlCommand.Parameters["@Str_Service_out_Invoice"].Value.ToString());
}
sqlTransaction.Commit();
sqlConnection.Close();
MessageBox.Show("Successfully Inserted");
}
catch (Exception
ex)
{
MessageBox.Show("Error");
}
}
Retrieving data using Stored procedure
public DataTable
StoredProcedureConnectionTest()
{
try
{
string ConnectionString = "ConnectionString";
SqlConnection sqlConnection = new SqlConnection();
sqlConnection.Open();
//The using statement is excellent for
handling important system resources
using (SqlCommand
sqlCommand = new SqlCommand())
{
sqlCommand.CommandText = "SP_SPNAME";
sqlCommand.CommandType = CommandType.StoredProcedure;
//Adding Parameter1
SqlParameter SqlParameter1 = new SqlParameter("@SqlParameter1", "SqlParameter1value1");
sqlCommand.Parameters.Add(SqlParameter1);
//Adding
Parameter2
SqlParameter SqlParameter2 = new SqlParameter("@SqlParameter2", "SqlParameter1value2");
sqlCommand.Parameters.Add(SqlParameter1);
DataTable dataTable = new DataTable();
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
sqlDataAdapter.Fill(dataTable);
return dataTable;
}
}
catch (Exception
ex)
{
MessageBox.Show("Error");
return null;
}
}
References :
http://www.dotnetperls.com/sqlclient 
