Three tier Architecture

Question

How I can perform Insert, Update and Delete in the database by three-tier architecture using only one Execute-NonQuery method without using SQLHelper in ado.net

Gnanavel Sekar · Answer

Try this also using System; using System.Data.SqlClient; using System.Data; using System.Configuration; namespace Profile_Database { public class Database { /// /// Variable Declaration /// /// #region Variable Declaration //private static bool connect = Convert.ToBoolean(ConfigurationSettings.AppSettings["ConnectionString"].ToString()); private static String OconString = ConfigurationSettings.AppSettings["ConnectionString"].ToString(); protected static SqlConnection Ocon; protected static SqlCommand Ocmd; protected static SqlTransaction transaction; protected static String queryString; protected static SqlDataReader dataReader; protected static DataSet ods; protected static SqlDataAdapter odr; int i; #endregion Variable Declaration /// /// Sql Database Coonection Open /// /// #region Connect public void JSconnect() { Ocon = new SqlConnection(); if (Ocon.State == ConnectionState.Closed) { Ocon.ConnectionString = OconString.ToString(); Ocon.Open(); } } #endregion Connect /// /// Sql Database Coonection Close /// /// #region Close public void JSclose() { if (Ocon.State == ConnectionState.Open) { Ocon.Close(); } } #endregion Close /// /// JSExecution /// /// #region Execution public int JSExecution(string JSSPname, string[] JSParameters, object[] JSParametervalue) { int i; Ocmd = new SqlCommand(); JSconnect(); Ocmd.CommandText = JSSPname; Ocmd.Connection = Ocon; Ocmd.CommandType = CommandType.StoredProcedure; SqlParameter param = new SqlParameter(); for (int cnt = 0; cnt { param = new SqlParameter(JSParameters[cnt], JSParametervalue[cnt]); Ocmd.Parameters.Add(param); } try { i = Ocmd.ExecuteNonQuery(); return i; } catch (Exception ex) { throw ex; } finally { JSclose(); } } #endregion Execution /// /// DataSet /// /// #region JSDataset public DataSet JSDataset(string JSSPname, string[] JSParameters, object[] JSParametervalue) { Ocmd = new SqlCommand(); JSconnect(); Ocmd.CommandText = JSSPname; Ocmd.Connection = Ocon; Ocmd.CommandType = CommandType.StoredProcedure; SqlParameter param = new SqlParameter(); for (int cnt = 0; cnt { param = new SqlParameter(JSParameters[cnt], JSParametervalue[cnt]); Ocmd.Parameters.Add(param); } try { odr = new SqlDataAdapter(Ocmd); ods = new DataSet(); odr.Fill(ods, "JSTable"); return ods; } catch (Exception ex) { throw ex; } finally { //ods.Clear(); JSclose(); } } #endregion JSDataset /// /// SqlDataReader /// /// #region JSDatareader public SqlDataReader JSDatareader(string JSSPname, string[] JSParameters, object[] JSParametervalue) { Ocmd = new SqlCommand(); JSconnect(); Ocmd.CommandText = JSSPname; Ocmd.Connection = Ocon; Ocmd.CommandType = CommandType.StoredProcedure; SqlParameter param = new SqlParameter(); for (int cnt = 0; cnt { param = new SqlParameter(JSParameters[cnt], JSParametervalue[cnt]); Ocmd.Parameters.Add(param); } try { SqlDataReader odr = Ocmd.ExecuteReader(); return odr; } catch (Exception ex) { throw ex; } finally { //JSclose(); } } #endregion JSDatareader } }

Joginder Banger · Answer

Hi, as your requirements I shared my project DAL(Data access layer ) class. using System; using System.Collections.Generic; using System.Configuration; using System.Data; using System.Data.SqlClient; using System.Linq; using System.Text; using System.Threading.Tasks; using INI; namespace DAL { public class DMLSql { private static SqlDataAdapter myAdapter; private static DMLSql Instance = null ; private DMLSql() { } private static object lockThis = new object(); private static SqlConnection conn; private static SqlConnection localConnection; private static string DatabaseName = string.Empty; private static string UserID = string.Empty; private static string Password = string.Empty; private static string ServerName = string.Empty; //Local connection details private static string localDatabaseName = string.Empty; private static string localUserID = string.Empty; private static string localPassword = string.Empty; private static string localServerName = string.Empty; // Initialise Connection public static DMLSql MYInstance { get { try { if (Instance == null ) { Instance = new DMLSql(); myAdapter = new SqlDataAdapter(); ReadINIFile objReadINIFile = new ReadINIFile(@ "D:\a-master.ini" ); //ReadINIFile objReadINIFile = new ReadINIFile(@"D:\NICSYNC.ini"); DatabaseName = objReadINIFile.GetSetting( "DatabaseName" , "DatabaseName" ); UserID = objReadINIFile.GetSetting( "UserName" , "UserName" ); Password = objReadINIFile.GetSetting( "Password" , "Password" ); ServerName = objReadINIFile.GetSetting( "ServerName" , "ServerName" ); localDatabaseName = objReadINIFile.GetSetting( "localDatabaseName" , "localDatabaseName" ); localUserID = objReadINIFile.GetSetting( "localUserName" , "localUserName" ); localPassword = objReadINIFile.GetSetting( "localPassword" , "localPassword" ); localServerName = objReadINIFile.GetSetting( "localServerName" , "localServerName" ); string LocalconncetionString = @ "Data Source=" + localServerName + ";Database=" + localDatabaseName + ";User ID=" + localUserID + ";Password=" + localPassword + ";" ; string TempConnectionstring = @ "Data Source=" + ServerName + ";Database=" + DatabaseName + ";User ID=" + UserID + ";Password=" + Password + ";" ; conn = new SqlConnection(TempConnectionstring); localConnection = new SqlConnection(LocalconncetionString); } return Instance; } catch (Exception) { throw ; } } } // Open Database Connection if Closed or Broken private SqlConnection openConnection(bool isLocalConnection) { SqlConnection Connection = null ; if (isLocalConnection) Connection = localConnection; else Connection = conn; if (Connection.State == ConnectionState.Closed || Connection.State == ConnectionState.Broken) Connection.Open(); return Connection; } // Close Connection.... private SqlConnection CloseConnection(bool isLocalConnection) { SqlConnection Connection = null ; if (isLocalConnection) Connection = localConnection; else Connection = conn; if (Connection.State == ConnectionState.Open || Connection.State == ConnectionState.Broken) { Connection.Close(); } return Connection; } /// /// Get A Single Records /// /// /// /// public DataTable GetSingleRecord(SqlCommand Cmd, CommandType commandType, bool isLocal = false ) { string Result = string.Empty; DataTable dt = new DataTable(); try { Cmd.Connection = openConnection(isLocal); Cmd.CommandType = commandType; dt.Load(Cmd.ExecuteReader()); } catch (SqlException) { CloseConnection(isLocal); throw ; } finally { CloseConnection(isLocal); } return dt; } // Insert data through Text/Procedure with sql parameters public int ExecuteNonquery(string query, SqlParameter[] sqlParameter, CommandType commandType,bool isLocal= false ) { int TempValue = 0; SqlCommand sqlCommand = new SqlCommand(); try { sqlCommand.Connection = openConnection(isLocal); sqlCommand.CommandText = query; sqlCommand.CommandType = commandType; if (sqlParameter != null ) sqlCommand.Parameters.AddRange(sqlParameter); TempValue = sqlCommand.ExecuteNonQuery(); } catch (SqlException ex) { CloseConnection(isLocal); throw ex; } finally { CloseConnection(isLocal); } return TempValue; } public int ExecuteNonquery(SqlCommand cmd, bool isLocal = false ) { int TempValue = 0; try { cmd.Connection = openConnection(isLocal); TempValue = cmd.ExecuteNonQuery(); } catch (SqlException ex) { CloseConnection(isLocal); throw ex; } finally { CloseConnection(isLocal); } return TempValue; } // To get Single Record with passing parameters public string GetSingleRecord(string query, SqlParameter[] sqlParameter, CommandType commandType, bool isLocal = false ) { string TempValue = string.Empty; SqlCommand sqlCommand = new SqlCommand(); try { sqlCommand.Connection = openConnection(isLocal); sqlCommand.CommandText = query; sqlCommand.CommandType = commandType; if (sqlParameter != null ) sqlCommand.Parameters.AddRange(sqlParameter); TempValue = Convert.ToString(sqlCommand.ExecuteScalar()); } catch (SqlException ex) { CloseConnection(isLocal); throw ex; } finally { CloseConnection(isLocal); } return TempValue; } // To get Single Record without passing parameters public string GetSingleRecord(string query, CommandType commandType, bool isLocal = false ) { string TempValue = string.Empty; SqlCommand sqlCommand = new SqlCommand(); try { sqlCommand.Connection = openConnection(isLocal); sqlCommand.CommandText = query; sqlCommand.CommandType = commandType; TempValue = Convert.ToString(sqlCommand.ExecuteScalar()); } catch (SqlException ex) { CloseConnection(isLocal); throw ex; } finally { CloseConnection(isLocal); } return TempValue; } // To get Single Record without passing parameters public string GetSingleRecord(SqlCommand cmd, bool isLocal = false ) { string TempValue = string.Empty; try { cmd.Connection = openConnection(isLocal); TempValue = Convert.ToString(cmd.ExecuteScalar()); } catch (SqlException ex) { CloseConnection(isLocal); throw ex; } finally { CloseConnection(isLocal); } return TempValue; } // To get multiple records with passing parameters public DataTable GetRecords(string query, SqlParameter[] sqlParameter, CommandType commandType, bool isLocal = false ) { SqlCommand sqlCommand = new SqlCommand(); DataTable records = null ; try { records = new DataTable(); sqlCommand.Connection = openConnection(isLocal); sqlCommand.CommandText = query; sqlCommand.CommandType = commandType; if (sqlParameter != null ) sqlCommand.Parameters.AddRange(sqlParameter); records.Load(sqlCommand.ExecuteReader()); } catch (SqlException ex) { CloseConnection(isLocal); throw ex; } finally { CloseConnection(isLocal); } return records; } public DataTable GetRecords(SqlCommand cmd, bool isLocal = false ) { DataTable records = null ; try { records = new DataTable(); cmd.Connection = openConnection(isLocal); records.Load(cmd.ExecuteReader()); } catch (SqlException ex) { CloseConnection(isLocal); throw ex; } finally { CloseConnection(isLocal); } return records; } // To get multiple records without passing parameters public DataTable GetRecords(string query, CommandType commandType, bool isLocal = false ) { SqlCommand sqlCommand = new SqlCommand(); DataTable records = null ; try { records = new DataTable(); sqlCommand.Connection = openConnection(isLocal); sqlCommand.CommandText = query; sqlCommand.CommandType = commandType; records.Load(sqlCommand.ExecuteReader()); } catch (SqlException ex) { CloseConnection(isLocal); throw ex; } catch (Exception ex) { CloseConnection(isLocal); throw ex; } finally { CloseConnection(isLocal); } return records; } } }

SARDAR MUKHTAR · Answer

//class Execution string result = cs.ExecuteCmd_WithOutput(cs.ReturnCommand("PROCEDURENAME", "@parameter1^@parameter2^@parameter3", "Value1^Value2^Value3", '^'), "@outputParameter"); //class : Data Access Layer -------------------------------- //Create Command Dynamically by using parameters public static SqlCommand ReturnCommand(string SP_Name, string Parameter, string Fields, char separator) { SqlCommand cmd = null; using (SqlConnection con = new SqlConnection(strConnectionString)) { try { string[] Param = Parameter.Split(separator); string[] Field = Fields.Split(separator); cmd = new SqlCommand(SP_Name, con); cmd.CommandType = CommandType.StoredProcedure; if (Param.Length == Field.Length) { for (int x = 0; x { if (Field[x].ToUpper() == "NULL") { cmd.Parameters.AddWithValue(Param[x].ToString(), DBNull.Value); } else if (Field[x].ToUpper() == "") { cmd.Parameters.AddWithValue(Param[x].ToString(), DBNull.Value); } else { cmd.Parameters.AddWithValue(Param[x].ToString(), Field[x].ToString()); } } } return cmd; } catch (Exception ee) { return cmd; } } } //Execute the Command ------------------- public string ExecuteCmd_WithOutput(SqlCommand cmd, string Out) { using (SqlConnection con = new SqlConnection(strConnectionString)) { try { cmd.Connection = con; SqlParameter par1 = new SqlParameter(Out, SqlDbType.VarChar, 200); par1.Direction = ParameterDirection.Output; cmd.Parameters.Add(par1); if (con.State == ConnectionState.Closed) { con.Open(); } cmd.ExecuteNonQuery(); string returnValue = Convert.ToString(cmd.Parameters[Out].Value); if (con.State == ConnectionState.Open) { con.Close(); } return returnValue; } catch (Exception ee) { if (con.State == ConnectionState.Open) { con.Close(); } return "ERROR"; } } }

Three tier Architecture

Answers (3)