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 commandTypebool 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; } } }