public static string SP_Tender_FindRecord(string ProcessId, ref DataSet ReturnDs) { SqlConnection sqlConn = new SqlConnection(); //defines database connection SqlCommand sqlCmd = new SqlCommand(); //defines what to do SqlDataAdapter sqlAdap = new SqlDataAdapter(); try { sqlConn.ConnectionString = ConfigurationManager.ConnectionStrings["AMS_MasterConnectionString"].ToString(); sqlConn.Open(); sqlCmd.CommandText = "SP_Tender_FindRecord"; sqlCmd.CommandType = CommandType.StoredProcedure; sqlCmd.Connection = sqlConn; SqlParameter parm1 = new SqlParameter("@ProcessId", SqlDbType.VarChar); parm1.Value = ProcessId; parm1.Direction = ParameterDirection.Input; sqlCmd.Parameters.Add(parm1); sqlAdap.SelectCommand = sqlCmd; sqlAdap.Fill(ReturnDs); return string.Empty; } catch (Exception err) { return err.Message; } finally { sqlConn.Close(); sqlConn.Dispose(); sqlAdap.Dispose(); } }
public DataSet FilterData(ArsonFilterModel filterData) { var connectionString = ConfigurationManager.ConnectionStrings["UCR_DataEntities"]; DataSet dsResult = new DataSet(); using (SqlConnection conn = new SqlConnection(connectionString.ConnectionString)) { try { SqlCommand command = new SqlCommand(); command.Connection = conn; command.CommandText = GenerateArsonORQuery(filterData); command.CommandType = System.Data.CommandType.Text; SqlDataAdapter adapter = new SqlDataAdapter(); adapter.SelectCommand = command; conn.Open(); adapter.Fill(dsResult); conn.Close(); adapter.Dispose(); command.Dispose(); } catch (Exception ex) { StorageClient.LogError(ex); } } return dsResult; }
/// <summary> /// Вывести в ds результат запроса select из command /// В случае ошибки вывести код ошибки. /// </summary> /// <param name="command"></param> /// <param name="ds"></param> /// <param name="ErrMsg"></param> /// <returns></returns> public static SqlState SqlViewTable(string Command, ref DataSet ResultDS, ref string ErrMsg) { SqlDataAdapter sql = new SqlDataAdapter(Command, ConnectString); ResultDS = new DataSet(); try { int count = sql.Fill(ResultDS); sql.Dispose(); if (count > 0) return SqlState.Success; else { ErrMsg = "Запрос не вернул ни одну запись."; return SqlState.EmptyResult; } } catch (Exception exp) { sql.Dispose(); ErrMsg = "Method: " + exp.TargetSite.Name.ToString() + "\r\nMessage: " + exp.Message + "\r\nSource: " + exp.Source + "\r\nCommand: " + Command; MessageBox.Show(ErrMsg, "SqlViewTable() Error!", MessageBoxButtons.OK, MessageBoxIcon.Error); return SqlState.Error; } finally { sql.Dispose(); } }
public static void NewBlogSubscribers() { SqlTriggerContext triggContext = SqlContext.TriggerContext; SqlCommand command = null; DataSet insertedDS = null; SqlDataAdapter dataAdapter = null; try { // Retrieve the connection that the trigger is using using (SqlConnection connection = new SqlConnection(@"context connection=true")) { connection.Open(); command = new SqlCommand(@"SELECT * FROM INSERTED;", connection); dataAdapter = new SqlDataAdapter(command); insertedDS = new DataSet(); dataAdapter.Fill(insertedDS); TriggerHandler(connection, ContentType.Blog, insertedDS); } } catch { } finally { try { if (command != null) { command.Dispose(); command = null; } if (dataAdapter != null) { dataAdapter.Dispose(); dataAdapter = null; } if (dataAdapter != null) { dataAdapter.Dispose(); dataAdapter = null; } if (insertedDS != null) { insertedDS.Dispose(); insertedDS = null; } } catch { } } }
/// <summary> /// Metodo que consulta todos los Conductores /// </summary> /// <returns>Retorna un DataTable con todos los Conductores</returns> public DataTable ConsultarConductores() { SqlCommand commandData = new SqlCommand(); System.Data.DataSet ds = new System.Data.DataSet(); DataTable dt = new DataTable(); OpenData("query"); string sqlconsulta = " SELECT * FROM [Conductor] with(nolock) "; commandData = new System.Data.SqlClient.SqlCommand(sqlconsulta, sqlConnData); commandData.CommandType = CommandType.Text; SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(commandData); try { da.Fill(dt); } catch (Exception) { ds = null; } finally { da.Dispose(); } CloseData(); return(dt); }
/// <summary> /// Metodo para consulta del historial de un viaje especifico /// </summary> /// <param name="id_viaje"></param> /// <returns>Retorna un DataTable con todos los registros</returns> public DataTable ConsultarViajesTracking(string id_viaje) { SqlCommand commandData = new SqlCommand(); System.Data.DataSet ds = new System.Data.DataSet(); DataTable dt = new DataTable(); OpenData("query"); string sqlconsulta = "SELECT [Id_viaje],[Ubicacion],[Observaciones] FROM [dbo].[Tracking] where Id_viaje = @Id_Viaje"; commandData = new System.Data.SqlClient.SqlCommand(sqlconsulta, sqlConnData); commandData.CommandType = CommandType.Text; commandData.Parameters.Add("@Id_Viaje", System.Data.SqlDbType.VarChar, 50); commandData.Parameters["@Id_Viaje"].Value = id_viaje.Trim(); //agregamos el parametro al query con el valor requerido SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(commandData); try { da.Fill(dt); } catch (Exception ex) { string test = ex.Message; ds = null; } finally { da.Dispose(); } CloseData(); return(dt); }
public DataSet GetDataSet(string a_strDataSource) //Select query { System.Data.SqlClient.SqlDataAdapter myAdapter; DataSet DS; try { DS = new DataSet(); myAdapter = new System.Data.SqlClient.SqlDataAdapter(a_strDataSource, GetConnectionString()); myAdapter.SelectCommand.CommandTimeout = ConnectionTimeOut; myAdapter.Fill(DS); myAdapter.Dispose(); myAdapter = null; return(DS); } catch (Exception ex) { ex.Data.Clear(); return(null); } finally { //DS.Dispose(); } }
//---------------------- methods ---------------------- public DataSet _dataSet_NoParameter(string queryString) { try { SqlConnection conn = new SqlConnection(); conn.ConnectionString = connectionString; if (conn.State == ConnectionState.Closed) conn.Open(); SqlCommand comm = new SqlCommand(queryString, conn); daRecords = new SqlDataAdapter(comm); daRecords.Fill(dsRecords); return dsRecords; } catch (SqlException er) { sqlUserError = er.Message; return dsRecords; } finally { dtRecords.Dispose(); daRecords.Dispose(); dsRecords.Dispose(); } }
public DataTable load_country() { try { if (care.State == ConnectionState.Closed) { care.Open(); } adapt = new SqlDataAdapter("fetchCountry", care); adapt.SelectCommand.CommandType = CommandType.StoredProcedure; DataSet ds = new DataSet(); adapt.Fill(ds, "tblCountry"); adapt.Dispose(); return ds.Tables["tblCountry"]; } catch { throw; } finally { care.Close(); } }
/// <summary> /// 获取某个用户及其下级 /// </summary> /// <param name="NReturn"></param> /// <param name="ExMsg"></param> /// <param name="UserID">UserID</param> /// <param name="ContainsSelf">是否包含自己</param> /// <param name="ContainsGrand">是否包含子孙</param> /// <returns></returns> public static DataTable ListChild(ref int NReturn, ref string ExMsg, long UserID, bool ContainsSelf, bool ContainsGrand) { DataSet ds = new DataSet(); using (SqlConnection SqlConn = new SqlConnection(Apq.DB.GlobalObject.SqlConnectionString)) { SqlDataAdapter sda = new SqlDataAdapter("dtxc.Apq_Users_ListChild", SqlConn); sda.SelectCommand.CommandType = CommandType.StoredProcedure; Apq.Data.Common.DbCommandHelper dch = new Apq.Data.Common.DbCommandHelper(sda.SelectCommand); dch.AddParameter("rtn", 0, DbType.Int32); dch.AddParameter("ExMsg", ExMsg, DbType.String, -1); dch.AddParameter("UserID", UserID, DbType.Int64); dch.AddParameter("ContainsSelf", ContainsSelf, DbType.Byte); dch.AddParameter("ContainsGrand", ContainsGrand, DbType.Byte); sda.SelectCommand.Parameters["rtn"].Direction = ParameterDirection.ReturnValue; sda.SelectCommand.Parameters["ExMsg"].Direction = ParameterDirection.InputOutput; SqlConn.Open(); sda.Fill(ds); NReturn = System.Convert.ToInt32(sda.SelectCommand.Parameters["rtn"].Value); ExMsg = sda.SelectCommand.Parameters["ExMsg"].Value.ToString(); sda.Dispose(); SqlConn.Close(); } return ds.Tables.Count > 0 ? ds.Tables[0] : null; }
public static DataSet ExecuteDataset(SqlConnection cn, SqlTransaction trans, string cmdText, string tableName, params SqlParameter[] sqlParams) { DataSet data = new DataSet(); SqlCommand cmd = new SqlCommand(cmdText, cn); cmd.CommandType = CommandType.Text; cmd.Transaction = trans; if (sqlParams != null) { AttachParameters(cmd, sqlParams); } SqlDataAdapter adapter = new SqlDataAdapter(cmd); if (tableName != null && tableName != string.Empty) { adapter.Fill(data, tableName); } else { adapter.Fill(data); } adapter.Dispose(); cmd.Parameters.Clear(); cmd.Dispose(); return data; }
public static DataSet ExecuteQuery(string sql) { try { //string connString = "Data Source=.\\SQLExpress; Integrated Security=true; User Instance=true; Initial Catalog=master;"; string connString = "Server=.\\SQLEXPRESS;Database=master;Integrated Security=SSPI;"; System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(); con.ConnectionString = connString; //DBBase.GetConnectionString(); con.Open(); System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(sql, con); DataSet ds1 = new DataSet(); da.Fill(ds1); da.Dispose(); con.Close(); con.Dispose(); return ds1; } catch (Exception ex) { Logger.LogQuery("SQL Exception:" + Environment.NewLine + Environment.NewLine + sql + Environment.NewLine + Environment.NewLine + ex.ToString(), true, true); throw; } }
protected void Page_Load(object sender, EventArgs e) { if (Session["kullanici"] != null || Session["yonetici"] != null) { } else { Response.Redirect("KullaniciGiris.aspx"); } try { Yardimci.baglanti.Open(); SqlCommand ogretmen_cek = new SqlCommand(); ogretmen_cek.CommandText = "SELECT * FROM OGRETMENLER_VIEW2"; ogretmen_cek.Connection = Yardimci.baglanti; DataSet ds = new DataSet(); SqlDataAdapter adp = new SqlDataAdapter(ogretmen_cek); adp.Fill(ds, "OGRETMENLER_VIEW2"); dwOgretmenler.DataSource = ds.Tables["OGRETMENLER_VIEW2"]; dwOgretmenler.DataBind(); ds.Dispose(); adp.Dispose(); Yardimci.baglanti.Close(); } catch (Exception hata) { Yardimci.baglanti.Close(); lbl_sonuc.Text = hata.Message; } }
// To get 'Curriculum' record of both 'Active','Inactive' type from database by stored procedure public DataTable LoadCurriculum(int LoggedInUser, string Ret) { SqlConnection Conn = new SqlConnection(ConnStr); // 'uspGetIntakeFormCurriculum' stored procedure is used to get both 'Active','Inactive' type records from Curriculum table SqlDataAdapter DAdapter = new SqlDataAdapter("Transactions.uspGetIntakeFormCurriculum", Conn); DAdapter.SelectCommand.CommandType = CommandType.StoredProcedure; DAdapter.SelectCommand.Parameters.AddWithValue("@LoggedInUser", LoggedInUser); DAdapter.SelectCommand.Parameters.AddWithValue("@RetMsg", Ret); DataSet DSet = new DataSet(); try { DAdapter.Fill(DSet, "Curriculum"); return DSet.Tables["Curriculum"]; } catch { throw; } finally { DSet.Dispose(); DAdapter.Dispose(); Conn.Close(); Conn.Dispose(); } }
//district public DataTable load_districts(Int16 stateId) { try { if (care.State == ConnectionState.Closed) { care.Open(); } adapt = new SqlDataAdapter("fetchDist", care); adapt.SelectCommand.CommandType = CommandType.StoredProcedure; adapt.SelectCommand.Parameters.AddWithValue("@stateId", stateId); DataSet ds = new DataSet(); adapt.Fill(ds, "tblDist"); adapt.Dispose(); return ds.Tables["tblDist"]; } catch { throw; } finally { care.Close(); } }
private void timtheotenbn() // Tìm kiếm dữ liệu theo mã { Ketnoi(); // Tạo kết nối file Access // Tạo Command SqlCommand objcommand = new SqlCommand(); objcommand.Connection = objcon; objcommand.CommandType = CommandType.Text; objcommand.CommandText = "Select * From tbbenhnhan Where tenbn LIKE '%" + txttimtheotenbn.Text + "%' ORDER BY tenbn"; //Tạo đối tượng Adapter SqlDataAdapter objAdapter = new SqlDataAdapter(); objAdapter.SelectCommand = objcommand; // Tạo DataTable nhận dữ liệu trả về DataTable objDataTable = new DataTable("abcd"); objAdapter.Fill(objDataTable); // Gán dữ liệu vào dataGrid dgwtim.DataSource = objDataTable; // Hủy các đối tượng objcommand.Dispose(); objcommand = null; objDataTable.Dispose(); objDataTable = null; objAdapter.Dispose(); objAdapter = null; // hủy kết nối }
public static DataTable getDropDownData(string tblFields, string tblName, string whereCond) { try { SqlCommand CMD = new SqlCommand(); CMD.Connection = ILODBConfig.GetConnection(); CMD.CommandType = CommandType.Text; CMD.CommandText = "Select " + tblFields + " From " + tblName + " where " + whereCond; SqlDataAdapter SDA = new SqlDataAdapter(CMD); DataTable DT = new DataTable(); SDA.Fill(DT); ILODBConfig.GetConnection().Dispose(); CMD.Connection.Dispose(); CMD = null; SDA.Dispose(); return DT; } catch (Exception ex) { throw new Exception(ex.ToString()); } finally { ILODBConfig.GetConnection().Close(); } }
public System.Data.DataSet ExecuteDataset(string sql) { var ds = new System.Data.DataSet(); var cmd = new System.Data.SqlClient.SqlCommand(sql, _connection); cmd.CommandTimeout = 700; System.Data.SqlClient.SqlDataAdapter da; try { OpenConnection(); //da = new SqlDataAdapter(sql, _connection); da = new System.Data.SqlClient.SqlDataAdapter(cmd); da.Fill(ds); da.Dispose(); CloseConnection(); } catch (Exception ex) { throw ex; } finally { da = null; CloseConnection(); } return(ds); }
ImportTemplateSettings TemplateSettings = new ImportTemplateSettings(); //application import settings public UpdateImportDatasetForQC(DataSet ds, ImportTemplateSettings ts) { DataForQCDataSet = new DataSet(); //start with clean dataset DataForQCDataSet = ds.Copy(); //Data for QC selection from the main data dataset TemplateSettings = ts; //Template settings //Get the list of pallets to QC string QCconnString = Properties.Settings.Default.ArchiveConnectionString; StringBuilder QCCommand_Text = new StringBuilder(); if (Properties.Settings.Default.Mode.ToString() == "Test") { QCquery = "select [Pallet_Number] FROM [QC_Archive_Test]"; // List of pallets for QC from test table; } else { QCquery = "select [Pallet_Number] FROM [QC_Archive]"; // List of pallets for QC; } System.Data.SqlClient.SqlConnection QCconn = new System.Data.SqlClient.SqlConnection(QCconnString); System.Data.SqlClient.SqlCommand QCcmd = new System.Data.SqlClient.SqlCommand(QCquery, QCconn); QCconn.Open(); DataSet QCDataSet = new DataSet(); // create data adapter System.Data.SqlClient.SqlDataAdapter QCda = new System.Data.SqlClient.SqlDataAdapter(QCcmd); // this will query the database and return the result to your datatable QCda.Fill(QCDataSet); QCconn.Close(); QCda.Dispose(); QCTable = QCDataSet.Tables[0]; //set the translation table QCDataSet.Dispose(); }
protected void Page_Load(object sender, EventArgs e) { try { string componentName = Request["componentName"]; string tokenName = Request["tokenName"]; string tokenValue = string.Empty; DataSet ds = new DataSet(); SqlCommand cmd = new SqlCommand("dbo.FSFPL_RestoreEditableContent"); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("ComponentName", componentName); cmd.Parameters.AddWithValue("TokenName", tokenName); SqlConnection conn = new SqlConnection(SiteConfig.ConnectionString); cmd.Connection = conn; SqlDataAdapter sqlda = new SqlDataAdapter(cmd); sqlda.Fill(ds); // return data for display tokenValue = ds.Tables[0].Rows[0]["TokenValue"].ToString(); // cleanup conn.Close(); ds.Dispose(); cmd.Dispose(); sqlda.Dispose(); Response.Write(tokenValue); } catch (Exception ex) { Response.Write(ex.Message); } }
public DataTable getData() { try { query = "PARTY_FETCHING"; if (con.State == ConnectionState.Closed) { con.Open(); } dap = new SqlDataAdapter(query, con); dap.SelectCommand.CommandType = CommandType.StoredProcedure; DataSet ds = new DataSet(); dap.Fill(ds, "temp"); dap.Dispose(); return ds.Tables["temp"]; } catch { throw; } finally { if(con.State == ConnectionState.Open) con.Close(); } }
private void loginButton_Click(object sender, RoutedEventArgs e) { string connetionString = null; SqlConnection connection; SqlCommand command ; SqlDataAdapter adapter = new SqlDataAdapter(); DataSet ds = new DataSet(); string sql = null; connetionString = "Data Source=130.237.226.220;Initial Catalog=starwars;User ID=charizard;Password=polka123"; sql = "SELECT * from Users where login="******"and password = "******";"; connection = new SqlConnection(connetionString); try { connection.Open(); command = new SqlCommand(sql, connection); adapter.SelectCommand = command; adapter.Fill(ds, "SQL Temp Table"); adapter.Dispose(); command.Dispose(); connection.Close(); foreach (DataTable tables in ds.Tables) { MessageBox.Show (tables.TableName); } } catch (Exception ex) { MessageBox.Show("Can not open connection ! "); } }
public DataTable getData(partyBO PartyBO) { try { query = "fetchParty"; if (con.State == ConnectionState.Closed) { con.Open(); } dap = new SqlDataAdapter(query, con); dap.SelectCommand.CommandType = CommandType.StoredProcedure; dap.SelectCommand.Parameters.AddWithValue("@countryId", PartyBO.countryId); DataSet ds = new DataSet(); dap.Fill(ds, "temp"); dap.Dispose(); return ds.Tables["temp"]; } catch { throw; } finally { if (con.State == ConnectionState.Open) con.Close(); } }
public System.Data.DataSet ExecuteDataset(string sql) { var ds = new System.Data.DataSet(); var cmd = new System.Data.SqlClient.SqlCommand(sql, _connection); cmd.CommandTimeout = 120; System.Data.SqlClient.SqlDataAdapter da; try { OpenConnection(); //da = new SqlDataAdapter(sql, _connection); da = new System.Data.SqlClient.SqlDataAdapter(cmd); da.Fill(ds); da.Dispose(); CloseConnection(); } catch (Exception ex) { //throw ex; DataTable objDt1 = new DataTable(); objDt1.Columns.Add("Code"); objDt1.Columns.Add("Message"); objDt1.Columns.Add("id"); objDt1.Rows.Add(1, ConfigurationManager.AppSettings["phase"] != null && ConfigurationManager.AppSettings["phase"].ToString().ToUpper() == "DEVELOPMENT" ? ex.Message : "Something went wrong.", ""); ds.Tables.Add(objDt1); } finally { da = null; CloseConnection(); } return(ds); }
public System.Data.DataTable sdatatable(string query) { System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection(); cn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["SQLConnstr"].ConnectionString; System.Data.DataTable dt = new System.Data.DataTable(); System.Data.SqlClient.SqlDataAdapter sda = new System.Data.SqlClient.SqlDataAdapter(); System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(query); cmd.CommandType = System.Data.CommandType.Text; cmd.Connection = cn; try { cn.Open(); sda.SelectCommand = cmd; try { sda.Fill(dt); } catch { } cn.Close(); cn.Dispose(); } finally { cn.Close(); sda.Dispose(); cn.Dispose(); } return(dt); }
/// <summary> /// ִ�д洢���̻�SQL��䣬������һ��DataTable /// </summary> /// <param name="strProcedureName">Ҫ��DataTable�Ĵ洢���̻�SQL���</param> /// <returns></returns> public DataTable ExcuteProcedure(string strProcedureName) { DataTable dt = new DataTable(); SqlConnection conn=null; try { conn = new SqlConnection(ConfigurationSettings.AppSettings.GetValues("connection")[0]); SqlDataAdapter sda = new SqlDataAdapter(strProcedureName, conn); DataSet ds = new DataSet(); conn.Open(); sda.Fill(ds); dt = ds.Tables[0]; sda.Dispose(); conn.Close(); conn.Dispose(); } catch (Exception ex) { Err MyErr = new Err(ex.Message.ToString()); } return dt; }
public static string GetProcessId(ref string ProcessId, string TenderNumber) { SqlConnection sqlConn = new SqlConnection(); //defines database connection SqlCommand sqlCmd = new SqlCommand(); //defines what to do SqlDataAdapter sqlAdap = new SqlDataAdapter(); try { sqlConn.ConnectionString = ConfigurationManager.ConnectionStrings["AMS_MasterConnectionString"].ToString(); sqlConn.Open(); sqlCmd.CommandText = "SELECT [TH_ProcessID] FROM [AMS_Master].[dbo].[Tender_Header] H WHERE [TH_NoTender] = '" + TenderNumber + "'"; sqlCmd.CommandType = CommandType.Text; sqlCmd.Connection = sqlConn; ProcessId = sqlCmd.ExecuteScalar().ToString(); return string.Empty; } catch (Exception err) { return err.Message; } finally { sqlConn.Close(); sqlConn.Dispose(); sqlAdap.Dispose(); } }
public static DataSet ExecuteDataset(SqlConnection cn, SqlTransaction trans, string cmdText, string tableName, params SqlParameter[] sqlParams) { DataSet data = new DataSet(); try { SqlCommand cmd = new SqlCommand(cmdText, cn); cmd.CommandType = CommandType.StoredProcedure; cmd.Transaction = trans; if (sqlParams != null) { AttachParameters(cmd, sqlParams); } SqlDataAdapter adapter = new SqlDataAdapter(cmd); if (tableName != null && tableName != string.Empty) { adapter.Fill(data, tableName); } else { adapter.Fill(data); } adapter.Dispose(); cmd.Parameters.Clear(); cmd.Dispose(); } catch (Exception err) { TScope.HandlError(err); } return data; }
protected void LinkButtonOffers_Click(object sender, EventArgs e) { PanelUsers.Visible = false; PanelOffers.Visible = true; PanelCredit.Visible = false; PanelCoupons.Visible = false; LinkButtonUsers.Enabled = true; LinkButtonOffers.Enabled = false; LinkButtonCredit.Enabled = true; LinkButtonCoupons.Enabled = true; DataTable dt = new DataTable(); DataSet ds = new DataSet(); SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["ShopConnectionString"].ConnectionString); SqlDataAdapter sda = new SqlDataAdapter("sp_statsOffers", sqlConn); sda.SelectCommand.CommandType = CommandType.StoredProcedure; sda.Fill(ds); dt = ds.Tables[0]; TimeClass tc = new TimeClass(); LabelOffersDate.Text = tc.ConvertToIranTimeString(Convert.ToDateTime(dt.Rows[0]["OffersDate"].ToString())); LabelOffersOffers.Text = dt.Rows[0]["OffersCount"].ToString(); LabelOffersActive.Text = dt.Rows[0]["OffersActive"].ToString(); LabelOffersPast.Text = dt.Rows[0]["OffersPast"].ToString(); LabelOffersSold.Text = dt.Rows[0]["OffersSoldCount"].ToString(); LabelOffersAverage.Text = (Convert.ToInt32(dt.Rows[0]["OffersSoldCount"].ToString()) / Convert.ToInt32(dt.Rows[0]["OffersPast"].ToString())).ToString(); sda.Dispose(); sqlConn.Close(); }
public DataTable getIssues(Int64 NUMBER,Int16 TYPE) { try { query = "ISSUES_FETCHING"; if (con.State == ConnectionState.Closed) { con.Open(); } dap = new SqlDataAdapter(query,con); dap.SelectCommand.CommandType = CommandType.StoredProcedure; dap.SelectCommand.Parameters.AddWithValue("@number",NUMBER) ; dap.SelectCommand.Parameters.AddWithValue("@type", TYPE); DataSet ds = new DataSet(); dap.Fill(ds,"temp"); dap.Dispose(); return ds.Tables["temp"]; } catch { throw; } finally { if(con.State == ConnectionState.Open) con.Close(); } }
// To get 'Program Lead(' record of 'Active' or 'Inactive' from database by stored procedure public DataTable LoadActiveProgramLead(int LoggedInUser, string returnmsg, bool IsActive) { SqlConnection Conn = new SqlConnection(ConnStr); // 'uspGetProgramLeadDetails' stored procedure is used to get specific records from Program Lead table SqlDataAdapter DAdapter = new SqlDataAdapter("uspGetProgramLeadDetails", Conn); DAdapter.SelectCommand.CommandType = CommandType.StoredProcedure; DAdapter.SelectCommand.Parameters.AddWithValue("@LoggedInUser", LoggedInUser); DAdapter.SelectCommand.Parameters.AddWithValue("@RetMsg", returnmsg); DAdapter.SelectCommand.Parameters.AddWithValue("@IsActive", IsActive); DataSet DSet = new DataSet(); try { DAdapter.Fill(DSet, "ProgramLead"); return DSet.Tables["ProgramLead"]; } catch { throw; } finally { DSet.Dispose(); DAdapter.Dispose(); Conn.Close(); Conn.Dispose(); } }
/// <summary> /// 下SQL Command取回資料 /// </summary> /// <param name="sSqlCmd">SQL Commnad String</param> /// <param name="sConnStr1">SQL Connection String</param> /// <returns>回傳經由SQL Command Select後的結果,傳回DataTable</returns> public DataTable execDataTable(string sSqlCmd, string sConnStr1) { SqlConnection conDB = new SqlConnection(); SqlDataAdapter sqlDataAdap = new SqlDataAdapter(); DataTable dttReturnData = new DataTable(); try { conDB = new SqlConnection(sConnStr1); sqlDataAdap = new SqlDataAdapter(sSqlCmd, conDB); sqlDataAdap.Fill(dttReturnData); } catch (Exception ex) { gLogger.ErrorException("DBConnection.execDataTable", ex); throw ex; } finally { if (sqlDataAdap != null) { sqlDataAdap.Dispose(); } if (conDB != null) { if (conDB.State == ConnectionState.Open) { conDB.Close(); } conDB.Dispose(); } } return dttReturnData; }
public static bool AppendDataTable(ref DataTable dsNewTable, string strSQL) { if (!OpenConnection()) { return(false); } try { DataTable oDataSet = new DataTable(); System.Data.SqlClient.SqlDataAdapter DataAdapter; DataAdapter = new System.Data.SqlClient.SqlDataAdapter(strSQL, lo_Connection); System.Data.SqlClient.SqlCommandBuilder myDataRowsCommandBuilder = new System.Data.SqlClient.SqlCommandBuilder(DataAdapter); DataAdapter.Update(dsNewTable); DataAdapter.Dispose(); myDataRowsCommandBuilder.Dispose(); return(true); } catch { return(false); } finally { lo_Connection.Close(); } }
public string getLocationInfoById(int LocationId) { DataTable dt = new DataTable(); DataSet ds = new DataSet(); SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["AppConnectionString"].ConnectionString); SqlDataAdapter sda = new SqlDataAdapter("sp_locationInfoByCityId", sqlConn); sda.SelectCommand.CommandType = CommandType.StoredProcedure; sda.SelectCommand.Parameters.Add("@LocationId", SqlDbType.Int).Value = LocationId; //try //{ sda.Fill(ds); dt = ds.Tables[0]; //} //catch (Exception ex) //{ //} //finally //{ sqlConn.Close(); sda.Dispose(); sqlConn.Dispose(); //} return dt.Rows[0]["CountryName"].ToString() + " - " + dt.Rows[0]["CityName"].ToString(); }
public static bool CloneDataTable(ref DataTable ReturnTable, string TableName) { System.Data.SqlClient.SqlDataAdapter lo_Ada = new System.Data.SqlClient.SqlDataAdapter(); DataTable Return_DataTable = new DataTable(); if (!OpenConnection()) { return(false); } try { System.Data.SqlClient.SqlCommand SqlCmd; SqlCmd = new System.Data.SqlClient.SqlCommand("Select TOP 1 * from " + TableName, lo_Connection); SqlCmd.Connection = lo_Connection; lo_Ada.SelectCommand = SqlCmd; lo_Ada.FillSchema(Return_DataTable, SchemaType.Source); lo_Ada.Dispose(); lo_Ada = null; ReturnTable = Return_DataTable; return(true); } catch { return(false); } finally { lo_Connection.Close(); } }
public Int32 getUserId(string VerificationCode) { DataTable dt = new DataTable(); DataSet ds = new DataSet(); SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["AppConnectionString"].ConnectionString); SqlDataAdapter sda = new SqlDataAdapter("sp_loginSessionRead", sqlConn); //try //{ sda.SelectCommand.CommandType = CommandType.StoredProcedure; sda.SelectCommand.Parameters.Add("@VerificationCode", SqlDbType.NVarChar).Value = VerificationCode; sda.Fill(ds); dt = ds.Tables[0]; //} //catch (Exception ex) //{ //} //finally //{ sqlConn.Close(); sda.Dispose(); sqlConn.Dispose(); //} if (dt.Rows.Count == 0) //no user found { return 0; } else { return Convert.ToInt32(dt.Rows[0]["UserId"].ToString()); } }
public System.Data.DataTable GetData(System.Data.SqlClient.SqlCommand cmd) { System.Data.DataTable dt = new System.Data.DataTable(); string strConnString = global::System.Configuration.ConfigurationManager.ConnectionStrings["SQLConnstr"].ConnectionString; System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(); con.ConnectionString = global::System.Configuration.ConfigurationManager.ConnectionStrings["SQLConnstr"].ConnectionString; System.Data.SqlClient.SqlDataAdapter sda = new System.Data.SqlClient.SqlDataAdapter(); cmd.CommandType = System.Data.CommandType.Text; cmd.Connection = con; try { con.Open(); sda.SelectCommand = cmd; sda.Fill(dt); return(dt); } catch (Exception ex) { return(dt); } finally { con.Close(); sda.Dispose(); con.Dispose(); } }
public async Task<List<WindowsDtls>> GetWindows() { var constring = @"Data Source='c:\users\u3696174\documents\visual studio 2012\Projects\CLSBforNode\CLSBforNode\App_Data\Database1.sdf'"; List<WindowsDtls> lst_result = new List<WindowsDtls>(); using (SqlConnection con=new SqlConnection(constring)) { await con.OpenAsync(); using (SqlCommand cmd=new SqlCommand()) { cmd.Connection = con; cmd.CommandText = @"select * from Windows"; cmd.CommandType = CommandType.Text; SqlDataAdapter adapter = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); adapter.Fill(dt); adapter.Dispose(); con.Close(); WindowsDtls obj_single = null; foreach (DataRow dr in dt.Rows) { obj_single = new WindowsDtls(); obj_single.ID = Convert.ToInt32( dr[0].ToString()); obj_single.Name = dr[1].ToString(); obj_single.Quantity = dr[2].ToString(); obj_single.Price = dr[3].ToString(); obj_single.Image = dr[4].ToString(); lst_result.Add(obj_single); obj_single = null; } } } return lst_result; }
public void BindGrid(GridView g, string query) { System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection(); cn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["SQLConnstr"].ConnectionString; System.Data.DataTable dt = new System.Data.DataTable(); System.Data.SqlClient.SqlDataAdapter sda = new System.Data.SqlClient.SqlDataAdapter(); System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(query); cmd.CommandType = System.Data.CommandType.Text; cmd.Connection = cn; try { cn.Open(); sda.SelectCommand = cmd; //try // { sda.Fill(dt); // } //catch{} g.DataSource = dt; g.AllowPaging = true; g.DataBind(); cn.Close(); cn.Dispose(); } finally { cn.Close(); sda.Dispose(); cn.Dispose(); } }
protected void Page_Load(object sender, EventArgs e) { if (Session["kullanici"] != null || Session["yonetici"] != null) { } else { Response.Redirect("KullaniciGiris.aspx"); } try { Yardimci.baglanti.Open(); SqlCommand sinif_cek = new SqlCommand(); sinif_cek.CommandText = "SELECT SINIF_ID, SINIF_AD FROM SINIFLAR"; sinif_cek.Connection = Yardimci.baglanti; DataSet ds = new DataSet(); SqlDataAdapter adp = new SqlDataAdapter(sinif_cek); adp.Fill(ds, "SINIFLAR"); dwSiniflar.DataSource = ds.Tables["SINIFLAR"]; dwSiniflar.DataBind(); ds.Dispose(); adp.Dispose(); Yardimci.baglanti.Close(); } catch (Exception hata) { Yardimci.baglanti.Close(); lbl_sonuc.Text = hata.Message; } }
public DataTable fetchAbuseIssueReport() { try { if (con.State == ConnectionState.Closed) { con.Open(); } query = "issuesReportedAsAbuse"; dap = new SqlDataAdapter(query, con); dap.SelectCommand.CommandType = CommandType.StoredProcedure; DataSet ds = new DataSet(); dap.Fill(ds, "temp"); dap.Dispose(); return ds.Tables["temp"]; } catch { throw; } finally { if (con.State == ConnectionState.Open) con.Close(); } }
private void txtPassword_KeyPress(object sender, System.Windows.Forms.KeyPressEventArgs e) { if (Strings.Asc(e.KeyChar) == 13) { modMain.logsuccess = false; modMain.sql = "select loginid,password from login"; modMain.comm = new System.Data.SqlClient.SqlCommand(modMain.sql, modMain.conn); System.Data.SqlClient.SqlDataAdapter adapt = new System.Data.SqlClient.SqlDataAdapter(modMain.comm); adapt.Fill(modMain.rslogin, "Login"); modMain.loguser = Strings.UCase(Strings.Trim(txtUser.Text)); modMain.logpass = Strings.Trim(txtPassword.Text); DataRow objdatarow; foreach (DataRow tempLoopVar_objdatarow in modMain.rslogin.Tables["Login"].Rows) { objdatarow = tempLoopVar_objdatarow; if (Strings.UCase(System.Convert.ToString(objdatarow["loginid"])) == modMain.loguser) { if (modMain.decrypt_pass(System.Convert.ToString(objdatarow["password"])) == (modMain.logpass)) { modMain.logsuccess = true; adapt.Dispose(); adapt = null; modMain.comm.Dispose(); modMain.comm = null; frmMain frm = new frmMain(); this.Hide(); frm.Show(); return; } } } if (modMain.logsuccess == false) { Interaction.MsgBox("Login Failed ? Please Check whether your login details are true", 0, null); txtUser.Focus(); } adapt.Dispose(); adapt = null; modMain.comm.Dispose(); modMain.comm = null; } }
public DataTable CreateDataTable(SqlCommand command) { DataTable dataTable = new DataTable(); System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(command); da.Fill(dataTable); da.Dispose(); da = null; return(dataTable); }
public static bool FillDataTable_ViaCmd(ref DataTable ReturnTable, ref SqlCommand SqlCmd) { System.Data.SqlClient.SqlDataAdapter lo_Ada = new System.Data.SqlClient.SqlDataAdapter(); DataTable Return_DataTable = new DataTable(); SqlConnection ActiveConn; if (!OpenConnection()) { return(false); } else { ActiveConn = lo_Connection; } SqlCmd.Connection = ActiveConn; SqlCmd.CommandTimeout = CommandTimeOutSeconds; int Retry = 2; while (Retry >= 0) { try { lo_Ada.SelectCommand = SqlCmd; lo_Ada.Fill(Return_DataTable); lo_Ada.Dispose(); lo_Ada = null; ActiveConn.Close(); ReturnTable = Return_DataTable; return(true); } catch (Exception ex) { if (Retry >= 1 && ex.Message.Contains("deadlock victim")) { System.Threading.Thread.Sleep(3337); Retry -= 1; } else if (Retry >= 1 && (ex.Message.Contains("INSERT EXEC failed ") || ex.Message.Contains("Schema changed "))) { System.Threading.Thread.Sleep(3337); Retry -= 1; } else { ActiveConn.Close(); Retry = -1; } } } return(false); }
public DataRow GetSP_Row(string ProcedureName) { this.cmd.CommandText = ProcedureName; this.cmd.CommandType = CommandType.StoredProcedure;//指定是存储过程 this.GenParameters(); System.Data.SqlClient.SqlDataAdapter ada = new System.Data.SqlClient.SqlDataAdapter(); ada.SelectCommand = (System.Data.SqlClient.SqlCommand)cmd; DataTable dt = new DataTable(); ada.Fill(dt); ada.Dispose(); return(dt.Rows.Count > 0 ? dt.Rows[0] : null); }
private void PopulateDS() { ds.Clear(); SqlAdapter = new System.Data.SqlClient.SqlDataAdapter ("SELECT AmounBrickID,AmounBrickName FROM View_EmployeesBricks WHERE Deleted IS NULL AND EmployeeID = " + Session["EmployeeID"], myConn); SqlAdapter.Fill(ds, "Bricks"); SqlAdapter = new System.Data.SqlClient.SqlDataAdapter ("SELECT RangeValue,RangeName FROM [AmounCRM2].[dbo].[NoOfPatients]", myConn); SqlAdapter.Fill(ds, "NoOfPatients"); SqlAdapter = new System.Data.SqlClient.SqlDataAdapter ("SELECT ShortName,[RangeValue] FROM [AmounCRM2].[dbo].[PrescriptionHappit]", myConn); SqlAdapter.Fill(ds, "PHabit"); SqlAdapter.Dispose(); }
private void FormBriefcaseFileNameInfo_Load(object sender, EventArgs e) { System.Data.SqlClient.SqlDataAdapter da = null; try { da = Vetting.InspectionType.GetAdapter(ActiveConnection); DataTable tbl = new DataTable(); da.Fill(tbl); this.cmbVettingTypes.DataSource = tbl; this.cmbVettingTypes.DisplayMember = "InspectionType"; } finally { da.Dispose(); } }
protected virtual void Dispose(bool disposing) { if (!this.disposedValue) { if (disposing) { } if (Conn.State == ConnectionState.Open) { Conn.Close(); } Conn.Dispose(); Command.Dispose(); Adapt.Dispose(); Parametros.Clear(); Parametros = null; Dt = null; } this.disposedValue = true; }
public void Change(DataTable tablechange) { if (string.IsNullOrWhiteSpace(tablechange.TableName)) { throw new Exception("ChangeSQL tablename error"); } SqlDataReader read = null; //SqlCommand cmd = null; SqlDataAdapter adapter = null; try { this.sqlMutex.WaitOne(); if (conn == null) { this.Open(); } adapter = new System.Data.SqlClient.SqlDataAdapter(); adapter.SelectCommand = new System.Data.SqlClient.SqlCommand("SELECT * FROM " + tablechange.TableName, this.conn); System.Data.SqlClient.SqlCommandBuilder cmdBldr = new System.Data.SqlClient.SqlCommandBuilder(adapter); adapter.UpdateCommand = cmdBldr.GetUpdateCommand(); //System.Data.SqlClient.SqlCommand cmd = cmdBldr.GetUpdateCommand(); //Console.WriteLine(cmd.ToString()); adapter.Update(tablechange); } finally { try { adapter.Dispose(); } catch { } if (transac == null) { this.Close(); } this.sqlMutex.ReleaseMutex(); } }
private void txtsmanid_KeyPress(object sender, System.Windows.Forms.KeyPressEventArgs e) { if (Strings.Asc(e.KeyChar) == 13) { bool user_correct; user_correct = false; modMain.sql = "select * from login"; modMain.comm = new System.Data.SqlClient.SqlCommand(modMain.sql, modMain.conn); System.Data.SqlClient.SqlDataAdapter adapt = new System.Data.SqlClient.SqlDataAdapter(modMain.comm); adapt.Fill(modMain.rslogin, "Login"); modMain.te1 = Strings.UCase(Strings.Trim(txtSmanid.Text)); DataRow objdatarow; foreach (DataRow tempLoopVar_objdatarow in modMain.rslogin.Tables["Login"].Rows) { objdatarow = tempLoopVar_objdatarow; if (Strings.UCase(System.Convert.ToString(objdatarow["salesmanid"])) == modMain.te1) { user_correct = true; } } if (user_correct == false) { objdatarow = null; modMain.comm.Dispose(); modMain.comm = null; //rslogin.Dispose() //rslogin = Nothing adapt.Dispose(); adapt = null; txtNewUser.Focus(); } else { Interaction.MsgBox("SalesManid " + modMain.te1 + " already exists", 0, null); txtSmanid.Focus(); } user_correct = false; } modMain.te1 = null; }
public static Boolean fillDataSet(ref DataSet dataSet, string dataTableName, string sCommandText) { System.Data.SqlClient.SqlConnection con = connect(); System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(sCommandText, con); System.Data.SqlClient.SqlDataAdapter adp = new System.Data.SqlClient.SqlDataAdapter(cmd); dataSet.Tables.Add(new DataTable(dataTableName)); try { adp.Fill(dataSet.Tables[dataTableName]); return(true); } catch (Exception ex) { throw ex; } finally { adp.Dispose(); cmd.Dispose(); disconnect(con); } }
/// <summary> /// Metodo de consulta de todos los viajes por estado /// </summary> /// <param name="soloactivos"></param> /// <returns>Retorna un DataTable con todos los registros</returns> public DataTable ConsultarViajes(bool soloactivos) { SqlCommand commandData = new SqlCommand(); System.Data.DataSet ds = new System.Data.DataSet(); DataTable dt = new DataTable(); OpenData("query"); string sqlconsulta = "SELECT [Id_viaje],c.Nombre,c.UserName, [Lugar_inicio], " + "[Lugar_final],[Descripcion],[Tiempoestimado],[Estado]" + "FROM[dbo].[Viaje] v with(nolock), Conductor c with(nolock)" + "where v.Identificacion = c.Identificacion "; if (soloactivos) { sqlconsulta += " and v.Estado='ACTIVO'"; } commandData = new System.Data.SqlClient.SqlCommand(sqlconsulta, sqlConnData); commandData.CommandType = CommandType.Text; //agregamos el parametro al query con el valor requerido SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(commandData); try { da.Fill(dt); } catch (Exception) { ds = null; } finally { da.Dispose(); } CloseData(); return(dt); }
bool Completed; //did the translation complete? public DataTranslator(DataSet ds, ImportTemplateSettings ImportSettings, List <String> tl, string gb) { Data2Translate = ds; TemplateSettings = ImportSettings; List2Translate = tl; GrowerBlock = gb; Completed = false; //Did tranlation complete? Not yet. try { //get Commodity data from database string connString = Properties.Settings.Default.ConnectionString; string query = "select [Data_Column_Name],[Description],[Value], [Custom_Value] FROM Translation_Validation_Table"; // WHERE Famous_Validate = 1"; System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connString); System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(query, conn); conn.Open(); DataSet commodityDataSet = new DataSet(); // create data adapter System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd); // this will query the database and return the result to your datatable da.Fill(commodityDataSet); conn.Close(); da.Dispose(); CommodityTable = commodityDataSet.Tables[0]; //set the translation table commodityDataSet.Dispose(); } catch (Exception e) { MessageBox.Show("There was an error while trying to load the Commodity data for translation. \n" + " Note what happened and contact administrator for help or see error log. \n"); Error_Logging el = new Error_Logging("There was an error while trying to load theCommodity data for translation. \n" + e); ds.Dispose(); return; } }
public static DataSet fillDataSet(string sCommandText) { System.Data.SqlClient.SqlConnection con = connect(); System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(sCommandText, con); System.Data.SqlClient.SqlDataAdapter adp = new System.Data.SqlClient.SqlDataAdapter(cmd); DataSet ds = new DataSet(); try { adp.Fill(ds); return(ds); } catch (Exception ex) { throw ex; } finally { adp.Dispose(); cmd.Dispose(); disconnect(con); } }
private void txtUser_KeyPress(object sender, System.Windows.Forms.KeyPressEventArgs e) { if (Strings.Asc(e.KeyChar) == 13) { bool user_correct; user_correct = false; modMain.sql = "select loginid,password from login"; modMain.comm = new System.Data.SqlClient.SqlCommand(modMain.sql, modMain.conn); System.Data.SqlClient.SqlDataAdapter adapt = new System.Data.SqlClient.SqlDataAdapter(modMain.comm); adapt.Fill(modMain.rslogin, "Login"); modMain.loguser = Strings.UCase(Strings.Trim(txtUser.Text)); DataRow objdatarow; foreach (DataRow tempLoopVar_objdatarow in modMain.rslogin.Tables["Login"].Rows) { objdatarow = tempLoopVar_objdatarow; if (Strings.UCase(System.Convert.ToString(objdatarow["loginid"])) == modMain.loguser) { user_correct = true; } } if (user_correct == true) { objdatarow = null; modMain.comm.Dispose(); modMain.comm = null; //rslogin.Dispose() //rslogin = Nothing adapt.Dispose(); adapt = null; txtPassword.Focus(); } else { Interaction.MsgBox("Invalid User Name", 0, null); } } }
public FamousXMLExporter(DataSet ds, ImportTemplateSettings ImS, List <String> dl) { Data2Export = ds.Copy(); exportStringList = dl; ImportSettings = ImS; try { //get Commodity data from database string connString = Properties.Settings.Default.ConnectionString; string query = "select [Commodity] FROM [StoneFuitCommodities]"; // List of stone fruits and berries; System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connString); System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(query, conn); conn.Open(); DataSet StoneFruitDataSet = new DataSet(); // create data adapter System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd); // this will query the database and return the result to your datatable da.Fill(StoneFruitDataSet); conn.Close(); da.Dispose(); StoneFruitTable = StoneFruitDataSet.Tables[0]; //set the translation table StoneFruitDataSet.Dispose(); } catch (Exception e) { MessageBox.Show("There was an error while trying to load the Commodity data for XML export. \n" + " Note what happened and contact administrator for help or see error log. \n"); Error_Logging el = new Error_Logging("There was an error while trying to load theCommodity data for XML export. \n" + e); ds.Dispose(); return; } }
public CombineMixedBoxesOnPallets(DataSet ds, ImportTemplateSettings ts, string name) { WorkingDataSet = ds; //dataset frpm import spreadsheet TemplateSettings = ts; //Import template settings FormName = name; try { //get Commodity data from database string connString = Properties.Settings.Default.ConnectionString; string query = "select [Data_Column_Name],[Description],[Value], [Custom_Value] FROM Translation_Validation_Table"; // WHERE Famous_Validate = 1"; System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connString); System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(query, conn); conn.Open(); DataSet commodityDataSet = new DataSet(); // create data adapter System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd); // this will query the database and return the result to your datatable da.Fill(commodityDataSet); conn.Close(); da.Dispose(); CommodityTable = commodityDataSet.Tables[0]; //set the translation table commodityDataSet.Dispose(); } catch (Exception e) { MessageBox.Show("There was an error while trying to load the Commodity data for the combining. \n" + " Note what happened and contact administrator for help or see error log. \n"); Error_Logging el = new Error_Logging("There was an error while trying to load theCommodity data. \n" + e); ds.Dispose(); return; } }
StringBuilder notTranslatedError = new StringBuilder(); //list to hold the name of fields that did not translate public DataTranslatorByGrower(DataSet ds, ImportTemplateSettings ImportSettings, List <String> tl, string gi, string pf) { Data2Translate = ds; TemplateSettings = ImportSettings; Prefix = pf; GrowerID = gi; // Completed = false; //Did tranlation complete? Not yet. if (Properties.Settings.Default.Mode == "Test") { GCVInfoTable = "GCV_Information_Test2"; TranslationDetailsTable = "Translation_Details_Test2"; } else { GCVInfoTable = "GCV_Information2"; TranslationDetailsTable = "Translation_Details2"; } /* Location in datarow of data to translate. * Grower_ID - 0, * Grower_Name - 1, * Grower_Commodity_Code - 2, * Commodity_Code - 3, * Commodity - 4, * Grower_Variety_Code - 5, * Variety_Code - 6, * Variety - 7, * Stone_Fruit - 8, * GCV_Code - 9, * Grower_Style_Code - 10, * Famous_Style_Code - 11, * Grower_Size_Code - 12, * Famous_Size_Code - 13, * Grower_Pack_Code - 14, * Famous_Pack_Code - 15, * Adams_Pack_Code - 16, * Grower_Label_Code - 17, * Famous_Label_Code - 18, * Adams_Label_Code - 19, * Grower_Grade_Code - 20, * Famous_Grade_Code - 21, * Adams_Grade_Code - 22, * Grower_Pallet_Type - 23, * Famous_Pallet_Type - 24, * Adams_Pallet_Type - 25 * */ try { //get Translation data from database string connString = Properties.Settings.Default.ConnectionString; string query = "SELECT Grower_ID, Grower_Name, Grower_Commodity_Code, Commodity_Code, Commodity, Grower_Variety_Code, " + "Variety_Code, Variety, Stone_Fruit, " + GCVInfoTable.ToString() + ".GCV_Code, Grower_Style_Code, " + "Famous_Style_Code, Grower_Size_Code, Famous_Size_Code, Grower_Pack_Code, " + "Famous_Pack_Code, Adams_Pack_Code, Grower_Label_Code, Famous_Label_Code, " + "Adams_Label_Code, Grower_Grade_Code, Famous_Grade_Code, " + "Adams_Grade_Code, Grower_Pallet_Type, Famous_Pallet_Type, " + "Adams_Pallet_Type " + "FROM " + GCVInfoTable.ToString() + " INNER JOIN " + TranslationDetailsTable.ToString() + " ON " + GCVInfoTable.ToString() + ".GCV_Code = " + TranslationDetailsTable.ToString() + ".GCV_Code " + "WHERE " + GCVInfoTable.ToString() + ".Grower_ID = " + GrowerID.ToString(); // Form translation table for grower; System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connString); System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(query, conn); conn.Open(); DataSet translationDataSet = new DataSet(); // create data adapter System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd); // this will query the database and return the result to the datatable da.Fill(translationDataSet); conn.Close(); da.Dispose(); TranslationTable = translationDataSet.Tables[0]; //set the translation table translationDataSet.Dispose(); } catch (Exception e) { MessageBox.Show("There was an error while trying to create and load the translation data. \n" + " Note what happened and contact administrator for help or see error log. \n"); Error_Logging el = new Error_Logging("There was an error while trying to create and load translation data. \n" + e); ds.Dispose(); return; } }
protected void dxcbpodfiles_Callback(object sender, DevExpress.Web.ASPxClasses.CallbackEventArgsBase e) { try { string _folderpath = ""; //int _orderno = 0; int _ordernocount = 0; Int32 _folderid = 0; bool _uploaded = false; //flagged if uploaded completed ok _folderpath = return_selected_folder(); _folderid = return_selected_folderid(_folderpath); _uploaded = this.dxhfmanager.Contains("upld") ? (bool)this.dxhfmanager.Get("upld") : false; //09012011 stored in ilist to save casting datatable for IN query //05012011 multiple order no's are stored in data table //_ordernocount = _ol.Rows.Count; //_orderno = wwi_func.vint(this.dxlblorderno1.Text.ToString()); //this.dxhfmanager.Contains("ord") ? wwi_func.vint(wwi_security.DecryptString(this.dxhfmanager.Get("ord").ToString(), "publiship")) : 0; //DataTable _ol = (DataTable)Session["orderlist"]; IList <Int32> _ol = (IList <Int32>)Session["orderlist"]; _ordernocount = _ol.Count; if (_uploaded & _folderid > 0 && _ordernocount > 0) //if (_uploaded & _folderid > 0 && _orderno > 0) { //copy files from temporary folder string _uid = wwi_security.DecryptString(this.dxhfmanager.Get("uid").ToString(), "publiship"); string _tempdir = DateTime.Now.ToShortDateString().Replace("/", "") + _uid; string _sourcepath = System.IO.Path.Combine("~\\documents\\not found", _tempdir); int _copies = file_transfer(_sourcepath, _folderpath, true); if (_copies > 0) { //update order table //save quote id to order table //if (_ordernocount == 1) //{ // //(DataRow)_ol.Rows[0][0] // SubSonic.Update upd2 = new SubSonic.Update(DAL.Logistics.Schemas.OrderTable); // recordsaffected = upd2.Set("document_folder").EqualTo(_folderid) // .Where("OrderNumber").IsEqualTo(_ol[0]) // .Execute(); //} //else //{ //string _csv = wwi_func.datatable_to_string(_ol, ","); string _csv = string.Join(",", _ol.Select(i => i.ToString()).ToArray()); //establish connection ConnectionStringSettings _cs = ConfigurationManager.ConnectionStrings["PublishipSQLConnectionString"]; SqlConnection _cn = new SqlConnection(_cs.ConnectionString); //create a sql data adapter System.Data.SqlClient.SqlDataAdapter _adapter = new System.Data.SqlClient.SqlDataAdapter(); //instantiate event handler _adapter.RowUpdated += new SqlRowUpdatedEventHandler(adapter_RowUpdated); _adapter.SelectCommand = new SqlCommand("SELECT [document_folder], [orderNumber] FROM OrderTable WHERE [OrderNumber] IN (" + _csv + ")", _cn); //populate datatable using selected order numbers DataTable _dt = new DataTable(); _adapter.Fill(_dt); //update document folder for each order number in _dt for (int ix = 0; ix < _dt.Rows.Count; ix++) { _dt.Rows[ix]["document_folder"] = _folderid; } //update command _adapter.UpdateCommand = new SqlCommand("UPDATE OrderTable SET [document_folder] = @document_folder WHERE [OrderNumber] = @OrderNumber;", _cn); _adapter.UpdateCommand.Parameters.Add("@OrderNumber", SqlDbType.Int).SourceColumn = "OrderNumber"; _adapter.UpdateCommand.Parameters.Add("@document_folder", SqlDbType.Int).SourceColumn = "document_folder"; _adapter.UpdateBatchSize = 5; //_ordernocount; _adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None; _adapter.Update(_dt); _adapter.Dispose(); _cn.Close(); //} if (_countUpdated > 0) //countupdated will return the number of batches sent NOT the number of records updated { //disable session expiration which occurs when a directory is deleted //uses System.Reflection PropertyInfo p = typeof(System.Web.HttpRuntime).GetProperty("FileChangesMonitor", BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Static); object o = p.GetValue(null, null); FieldInfo f = o.GetType().GetField("_dirMonSubdirs", BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.IgnoreCase); object monitor = f.GetValue(o); MethodInfo m = monitor.GetType().GetMethod("StopMonitoring", BindingFlags.Instance | BindingFlags.NonPublic); m.Invoke(monitor, new object[] { }); //kill temp folder System.IO.Directory.Delete(Server.MapPath(_sourcepath), true); this.dxlblinfo2.Text += _copies + " files have been uploaded to folder " + _folderid + " and linked to order numbers(s) " + _csv; this.dxpnlinfo.Visible = true; } else { this.dxlblerr.Text = "We have not been able to link order number(s) to online folder " + _folderid + " please refer to Publiship I.T."; this.dxpnlerr.Visible = true; } //end check records affected } } //end update order table } catch (Exception ex) { this.dxlblerr.Text = ex.Message.ToString(); this.dxpnlerr.Visible = true; } }
public bool Validate() //Validation routine { DataRow[] FoundValidateRows; DataRow[] CommodityValidateRows; Passed = false; int rownumber = 0; string sTempField; DataTable DatTable = new DataTable(); DatTable = Data2Validate.Tables[0]; DataSet ds = new DataSet(); try { //get Validation data from database string connString = Properties.Settings.Default.ConnectionString; string query = "select [Data_Column_Name],[Description],[Value], [Custom_Value], [Famous_Validate], [Ignore] from Translation_Validation_Table"; // WHERE Famous_Validate = 1"; System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connString); System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(query, conn); conn.Open(); // create data adapter System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd); // this will query the database and return the result to your datatable da.Fill(ds); conn.Close(); da.Dispose(); ValidationTable = ds.Tables[0]; //set the translation table ds.Dispose(); } catch (Exception e) { MessageBox.Show("There was an error while trying to load the Validation table. \n" + " Note what happened and contact administrator for help or see error log. \n"); Error_Logging el = new Error_Logging("There was an error while trying to load the Validation Table. \n" + e); ds.Dispose(); return(false); } try { //Validate values generic routine ************************************************************************* //Takes the list of fields to evaluate and iterates through each of them. foreach (string sField in List2Validate) { rownumber = 0; //This is for the case where the size and style are contatonated into one field in the data streadsheet. *** if ((TemplateSettings.SizeColumn == TemplateSettings.StyleColumn) & ((sField == "Size") || (sField == "Style"))) { sTempField = "PackCode"; } else { sTempField = sField; } //*** *** FoundValidateRows = ValidationTable.Select("Data_Column_Name = " + "'" + sTempField + "'"); //get all rows for sField from validation table CommodityValidateRows = ValidationTable.Select("Data_Column_Name = 'Commodity'"); //get all rows for Commodity from validation table foreach (DataRow row in Data2Validate.Tables[0].Rows) //interate through data rows and check the sField column { Passed = false; foreach (DataRow validaterow in FoundValidateRows) //FoundValidateRows contains all the validation rows for sField { if (row[TemplateSettings.DataColumnLocation(sField).Column].ToString().Trim() == validaterow[ValidateWhat].ToString().Trim() && validaterow[4].ToString() == "1") //Validate what tells //it to check value or description in validate table { Passed = true; //If found, set passed to true } else if (sField == "Grade") //special case for grade. Stone Fruit has no grade and should not be validated { foreach (DataRow Commodityrow in CommodityValidateRows) { if (row[TemplateSettings.CommodityColumn].ToString().Trim() == Commodityrow[2].ToString() && Commodityrow[3].ToString() == "Stone Fruit") { Passed = true; //If stone fruit and Grade, set passed to true as grade is not validated } } } } if (!Passed) //if it failed the validation add the location in the spreadsheet to the invalid list { if (rownumber >= TemplateSettings.DataColumnLocation(sField).Row) //The sField row parm is the start row. { if (!((TemplateSettings.SizeColumn == TemplateSettings.StyleColumn) & (sField == "Style"))) //Skip for second cycle for case of Style_Size field { // data as it will cycle for both size and style. InvalidItemList.Add(new DataItemLocation(rownumber, TemplateSettings.DataColumnLocation(sField).Column)); //add location of all failures to list. } } } if (InvalidItemList.Count() > 0) //if there were any invalid entries, it did not pass { Passed = false; } rownumber++; } } //Check for missing pack code, fumigation, hatch, deck values and duplicate Tag Numbers. rownumber = 0; foreach (DataRow row in Data2Validate.Tables[0].Rows) //interate through data rows and validate other fields { Passed = false; if (String.IsNullOrEmpty(row[TemplateSettings.PackCodeColumn].ToString())) //check for empty pack codes { InvalidItemList.Add(new DataItemLocation(rownumber, TemplateSettings.PackCodeColumn)); //add location of failures to list. } if (String.IsNullOrEmpty(row[TemplateSettings.HatchColumn].ToString())) //check for empty hatch { InvalidItemList.Add(new DataItemLocation(rownumber, TemplateSettings.HatchColumn)); //add location of failures to list. row[TemplateSettings.HatchColumn] = " "; } if (String.IsNullOrEmpty(row[TemplateSettings.DeckColumn].ToString())) //check for empty Deck { InvalidItemList.Add(new DataItemLocation(rownumber, TemplateSettings.DeckColumn)); //add location of failures to list. row[TemplateSettings.DeckColumn] = " "; } if (row[TemplateSettings.TagNumberColumn].ToString().Trim().Length > 12 || row[TemplateSettings.TagNumberColumn].ToString().Trim().Length < 1) //check for too long too short of tag numbers. { InvalidItemList.Add(new DataItemLocation(rownumber, TemplateSettings.TagNumberColumn)); //add location of failures to list. } if (InvalidItemList.Count() > 0) //if there were any invalid entries, it did not pass { Passed = false; } if (String.IsNullOrEmpty(row[TemplateSettings.FumigatedColumn].ToString())) //check for empty fumigation { InvalidItemList.Add(new DataItemLocation(rownumber, TemplateSettings.FumigatedColumn)); //add location of failures to list. } else if (!(row[TemplateSettings.FumigatedColumn].ToString() == "1" || row[TemplateSettings.FumigatedColumn].ToString() == "0")) { InvalidItemList.Add(new DataItemLocation(rownumber, TemplateSettings.FumigatedColumn)); //add location of failures to list. } //check Tag Numbers if (TagNumberExist.TagNumberExist(row[TemplateSettings.TagNumberColumn].ToString().Trim())) { InvalidItemList.Add(new DataItemLocation(rownumber, TemplateSettings.TagNumberColumn)); } rownumber++; } } catch (Exception e) { MessageBox.Show("Data Validation process had an error. Please note what was done and see administrator for help or see error log. \n"); Error_Logging el = new Error_Logging("Data Validation process had an error. \n" + e); return(false); } if (InvalidItemList.Count() > 0) //if there were any invalid entries, it did not pass { Passed = false; } else { Passed = true; } return(Passed); } //end of Validate Method