class DataAcess { SqlConnection con; //spName:name of stored procedure //spParams:parameters required for stored procedure. public DataSet ExecuteDataSet(string spName, object[] spParams) { try { SqlCommand sqlcom = GetSqlCommand(spName, spParams); SqlDataAdapter sqlda = new SqlDataAdapter(sqlcom); DataSet dsetResult = new DataSet(); sqlda.Fill(dsetResult); return dsetResult; } finally { CloseConnection(); } } private SqlCommand GetSqlCommand(string spName, object[] spParam) { SqlCommand com = new SqlCommand(spName, GetConnection()); com.CommandType = CommandType.StoredProcedure; //SqlCommandBuilder automatically derive the parameters SqlCommandBuilder.DeriveParameters(com); com.Parameters.RemoveAt(0); if (spParam != null || spParam.Length != 0) { for (int index = 0; index < spParam.Length; index++) { //value will be set if (spParam[index] != null) com.Parameters[index].Value = spParam[index]; else com.Parameters[index].Value = DBNull.Value; } } return com; } //read the connection string from web.config(web) /app.config(window) private SqlConnection GetConnection() { con = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["MyConn"]); con.Open(); return con; } private void CloseConnection() { con.Close(); }}
private void DisplayMachine(string machineName) { DataAcess dataAcess = new DataAcess(); //parameters required for procedure,if no parameter then pass Null object[] spparam = { machineName }; DataSet ds = dataAcess.ExecuteDataSet("Usp_DisplayMachine",spparam); }