C# – Database Connections and Manipulations [ADO.NET]

Standard

.Net has the feature to connect and manipulate various database using different kinds of namespace and functionality  such as SqlClient (Native client for the SQL Server), OLEDBProvider (Object Linking Embedding data base provider , ODBC (Open database connectivity) and Oracle provider.

System.Data.SqlClient namespace support to create and open the connection with SQL server, execute the Sql command (Either plain SQL text or Stored Procedure or Table direct) on it. Various execution methods such as

1. ExecuteReader (Execute the Command and return multiple values in SqlDataReader),

2. ExecuteScalar (Execute the command and return single object value) and

3. Execute Non Query (Execute the command but not return anything)

Here I have compiled all these features in this tiny methods with meaningful but long name (Just for understanding real life software development we have to use shortest but meaningful name. Variable name or method name some other programming part should written in English it will helpful to understand by programmers in other region. Program variables written in such as Malay, German … but using english (scripting) letters, unable to understand by the programmers in other region. So, program loose it global standard. I have that experience, this scenario, only debug tool and online translator may help us to move the task )

using System;

using System.Data.SqlClient;

using System.Data;

using System.Xml;

using System.Collections;

namespace dbSqlClient

{

class Program

{

static void Main(string[] args)

{

//Methods are named with Long name. Just for understanding.

string strConnectionString = @”Data Source=THOSTHISINS02\SQLEXPRESS;Initial Catalog=test;Integrated Security=True;Pooling=False”;

//executeReaderUsingProcedureReturnMultipleValue(strConnectionString, “StoredProcedure1”);

//executeScalarReturnSingleValue(strConnectionString);

//executeNonQueryInsertOrUpdateOrDeleteStatement(strConnectionString);

usingDataAdapterToSetInDataset(strConnectionString);

//usingXmlReaderForXmlData(strConnectionString);

//usingSqlTransactionToBeginRollbackCommit(strConnectionString);

//executeReaderWithInOutParameter(strConnectionString,”SPGetStudentListBySubject”);

Console.ReadLine();

}

static void executeScalarReturnSingleValue(string strConnectionString)

{

using (SqlConnection sqlCn = new SqlConnection(strConnectionString))

{

sqlCn.Open();

SqlCommand sqlCmd = new SqlCommand(“Select count(*) from Subjects”, sqlCn);

sqlCmd.CommandType = CommandType.Text;

Object o = sqlCmd.ExecuteScalar();

Console.WriteLine(” {0}”, o.ToString());

sqlCn.Close();

}// End of the SqlConnection scope

}

static void executeNonQueryInsertOrUpdateOrDeleteStatement(string strConnectionString)

{

using (SqlConnection sqlCn = new SqlConnection(strConnectionString))

{

sqlCn.Open();

SqlCommand sqlCmd = new SqlCommand(“insert Test1 values (9, ‘K’)”, sqlCn);

sqlCmd.CommandType = CommandType.Text;

int intRowsAffected = sqlCmd.ExecuteNonQuery();

Console.WriteLine(” {0}”, intRowsAffected.ToString());

sqlCn.Close();

}// End of the SqlConnection scope

}

static void executeReaderUsingProcedureReturnMultipleValue(string strConnectionString, string strProcedureName)

{

using (SqlConnection sqlCn = new SqlConnection(strConnectionString))

{

sqlCn.Open();

SqlCommand sqlCmd = new SqlCommand();

sqlCmd.Connection = sqlCn;

sqlCmd.CommandText = strProcedureName;

sqlCmd.CommandType = CommandType.StoredProcedure;

SqlDataReader sqlReader = sqlCmd.ExecuteReader(CommandBehavior.CloseConnection);

while (sqlReader.Read())

{

Console.WriteLine(” {0} \t {1}”, sqlReader[0].ToString(), sqlReader[1].ToString());

}

sqlReader.Close();

}

}

static void executeReaderWithInOutParameter(string strConnectionString, string strProcedureName)

{

using (SqlConnection sqlCn = new SqlConnection(strConnectionString))

{

sqlCn.Open();

SqlCommand sqlCmd = new SqlCommand();

sqlCmd.Connection = sqlCn;

sqlCmd.CommandText = strProcedureName;

sqlCmd.CommandType = CommandType.StoredProcedure;

//Create parameter then assign other properties

//Add into the sqlCommand parameters collection

SqlParameter paramNumberOfStudent = new SqlParameter();

paramNumberOfStudent.ParameterName = “@paramNumberOfStudent”;

paramNumberOfStudent.Direction = ParameterDirection.Output;

paramNumberOfStudent.DbType = DbType.Int32;

paramNumberOfStudent.Value = 0;

sqlCmd.Parameters.Add(paramNumberOfStudent);

//Create the new parameter with its properties directly

//inside the method

sqlCmd.Parameters.Add(new SqlParameter(“@paramSubjectName”,

SqlDbType.NVarChar, 100, ParameterDirection.Input ,

false,0, 0, “Subject_name”,DataRowVersion.Default, “Tamil”)) ;

SqlDataReader sqlReader = sqlCmd.ExecuteReader(CommandBehavior.CloseConnection);

while (sqlReader.Read())

{

Console.WriteLine(” {0} \t {1}”, sqlReader[0].ToString(), sqlReader[1].ToString());

}

//Console.WriteLine(@”Would not get the value. It will return null {0} “,

//sqlCmd.Parameters[“@paramNumberOfStudent”].Value.ToString());

//http://support.microsoft.com/kb/308621

sqlReader.Close(); //Sql output parameter value only appear aftee close the reader;

Console.WriteLine(sqlCmd.Parameters[“@paramNumberOfStudent”].Value.ToString());

}

}

static void usingXmlReaderForXmlData(string strConnectionString)

{

using (SqlConnection sqlCn = new SqlConnection(strConnectionString))

{

sqlCn.Open();

SqlCommand sqlCmd = new SqlCommand();

sqlCmd.Connection = sqlCn;

sqlCmd.CommandType = CommandType.Text;

sqlCmd.CommandText = “Select * from Student Where Class_id in (9, 10, 11) for XML Auto”;

XmlReader xmlReader = sqlCmd.ExecuteXmlReader();

xmlReader.Read();

string data = string.Empty;

do

{

data = xmlReader.ReadOuterXml();

if (!(string.IsNullOrEmpty(data)))

Console.WriteLine(data);

}

while (!(string.IsNullOrEmpty(data)));

xmlReader.Close();

sqlCn.Close();

}// End of the SqlConnection scope

}

static void usingDataAdapterToSetInDataset(string strConnectionString)

{

using (SqlConnection sqlCn = new SqlConnection(strConnectionString))

{

sqlCn.Open();

sqlCn.CreateCommand();

DataSet ds = new DataSet();

SqlDataAdapter sda = new SqlDataAdapter(“Select * from Student where class_id in (7, 8) order by class_id”, sqlCn);

sda.Fill(ds);

foreach (DataRow dr in ds.Tables[0].Rows)

{

foreach (DataColumn dc in ds.Tables[0].Columns)

{

Console.Write(“{0} : {1}\t”, dc.ColumnName, dr[dc.ColumnName].ToString());

}

Console.WriteLine(“\r\n”);

}

sqlCn.Close();

}

}

 

static void usingSqlTransactionToBeginRollbackCommit(string sqlConnectionString)

{

using (SqlConnection sqlCn = new SqlConnection(sqlConnectionString))

{

sqlCn.Open();

SqlTransaction sqlTransaction = sqlCn.BeginTransaction();

SqlCommand sqlCommand = sqlCn.CreateCommand();

try

{

sqlCommand.CommandText = “Update Test1 set Col1 = ‘P’ where ID = 19”;

sqlCommand.CommandType = CommandType.Text;

sqlCommand.Transaction = sqlTransaction;

sqlCommand.ExecuteNonQuery();

sqlTransaction.Commit();

}

catch (Exception Ex)

{

Console.WriteLine(“Issue in commit the transaction {0} “, Ex.ToString());

try

{

sqlTransaction.Rollback();

}

catch (Exception ex)

{

Console.WriteLine(“Issue in rollback the transaction {0} “, ex.ToString());

}

}

finally

{

sqlCn.Close();

sqlTransaction.Dispose();

}

}

}

}

}

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s