protected void Page_Load(object sender, EventArgs e) { if (Session["userID"] == null) { Response.Redirect("ADLogin.aspx", true); } if (!IsPostBack) { if (PreviousPage != null && !IsCrossPagePostBack)//如果是Server.Transfer()轉來的網頁IsCrossPagePostBack就會是false ViewState["ShopID"] = PreviousPage.ShopID; else { Response.Redirect("~/Index.aspx"); Response.End(); } Remark.Attributes["placeholder"] = "在這裡輸入你要對訂購人說的話"; for (int i = 0 ; i < 13 ; i++) { Hour.Items.Add(new ListItem(i.ToString().PadLeft(2, '0'))); Minute.Items.Add(new ListItem(i.ToString().PadLeft(2, '0'))); } for (int i = 13 ; i < 24 ; i++) { Hour.Items.Add(new ListItem(i.ToString())); Minute.Items.Add(new ListItem(i.ToString())); } for (int i = 25 ; i < 60 ; i++) { Minute.Items.Add(new ListItem(i.ToString())); } Hour.SelectedValue = DateTime.Now.Hour.ToString().PadLeft(2, '0'); Minute.SelectedValue = DateTime.Now.Minute.ToString().PadLeft(2, '0'); //查店名 using (SqlConnection conn = new SqlConnection(DBTools.ConnectionString)) { conn.Open(); using (SqlCommand cmd = new SqlCommand(@"SELECT ShopName FROM ShopHead where ShopID=@ShopID", conn)) { cmd.Parameters.Add(new SqlParameter("@ShopID", SqlDbType.UniqueIdentifier)); cmd.Parameters[0].Value = PreviousPage.ShopID; using (SqlDataReader dr = cmd.ExecuteReader()) { dr.Read(); ShopName.Text = HttpUtility.HtmlEncode(dr.GetString(0)); } cmd.Cancel(); } } } }
protected void Button1_Click(object sender, EventArgs e) { if (TextBox1.Text.Trim() != "") { SqlConnection cnn = new SqlConnection(); cnn.ConnectionString = "Data Source=IDEA-PC\\sqlexpress;Initial Catalog=AspNet_LK;Integrated Security=True"; try { cnn.Open(); } catch (Exception ex) { Label1.Text = ex.Message; } if (cnn.State.ToString() == "Open") Label1.Text = "Соединение открыто"; SqlCommand comm = new SqlCommand(); comm.CommandType = System.Data.CommandType.Text; comm.CommandText = "INSERT INTO Discussion ([Message],[n_ls]) VALUES ('"+ TextBox1.Text.Trim() +"'," + n_ls.ToString() + ")"; comm.Connection = cnn; comm.ExecuteNonQuery(); comm.Cancel(); cnn.Close(); GridView1.DataBind(); } TextBox1.Text = ""; }
protected void editOrderButton_Click(object sender, EventArgs e) { using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CN_UPS"].ConnectionString)) { int loopCounter = 0; System.Data.SqlClient.SqlDataReader reader = null; System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(); //check for duplicates String query = "SELECT * FROM [ROReport_ProductArrival]"; cmd.CommandTimeout = 300; cmd.CommandText = query; cmd.CommandType = System.Data.CommandType.Text; cmd.Connection = conn; conn.Open(); //cmd.Parameters.AddWithValue("@Order_Number", messageLabel1.Text); Console.Write("Executing Reader"); reader = cmd.ExecuteReader(); while (reader.Read()) { loopCounter++; } reader.Close(); cmd.Parameters.Clear(); cmd.Cancel(); cmd.Dispose(); conn.Close(); } }
public ShowOutput(int id, SqlConnection sql) { InitializeComponent(); Title = "Job " + id; SqlCommand cmd = new SqlCommand("SELECT SharedDir+'\\'+Strings.s as Filename,stdout,stderr " + "FROM Data,Strings,Experiments " + "WHERE FilenameP=Strings.ID AND Data.ExperimentID=Experiments.ID " + "AND Data.ID=" + id, sql); cmd.CommandTimeout = 0; SqlDataReader r = cmd.ExecuteReader(); if (!r.Read()) throw new Exception("Could not read from SQL server."); Object sOut = r["stdout"]; Object sErr = r["stderr"]; if (sOut == DBNull.Value) textBoxOut.Text = "*** NO OUTPUT SAVED ***"; else textBoxOut.Text = (string)sOut; if (sErr == DBNull.Value) textBoxErr.Text = "*** NO OUTPUT SAVED ***"; else textBoxErr.Text = (string)sErr; textBoxFn.Text = (string) r["Filename"]; cmd.Cancel(); r.Close(); }
protected void Button1_Click1(object sender, EventArgs e) { SqlConnection conn_graph = new SqlConnection(WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString); //SqlCommand cmd_graph = new SqlCommand("SELECT Account_name, COUNT(Account_name) AS count FROM [Account_Order_M_View] WHERE (Order_date like '%" + DropDownList1.Text + "%') GROUP BY Account_name", conn_graph); SqlCommand cmd_graph = new SqlCommand("SELECT Account_name, COUNT(Account_name) AS count FROM [Account_Order_M_View] WHERE (Order_date like @Order_date) GROUP BY Account_name", conn_graph); cmd_graph.Parameters.AddWithValue("@order_date", DropDownList1.Text.Trim() + "%"); SqlDataReader dr_graph = null; try //==== 以下程式,只放「執行期間」的指令!===================== { conn_graph.Open();//---- 這時候才連結DB dr_graph = cmd_graph.ExecuteReader();//---- 這時候執行SQL指令,取出資料 Chart1.DataSource = dr_graph; Chart1.DataBind(); } catch (Exception ex_graph) //---- 如果程式有錯誤或是例外狀況,將執行這一段 { Response.Write("error" + ex_graph.ToString()); } finally //---- 關掉資料連結 { if (dr_graph == null) { cmd_graph.Cancel(); } if (conn_graph.State == ConnectionState.Open) { conn_graph.Close(); conn_graph.Dispose(); } } }
public static void ejecutarNonQuery(SqlCommand comandoo, String unaQuery) { comandoo.Cancel(); comandoo.CommandText = unaQuery; comandoo.CommandTimeout = 21600; comandoo.ExecuteNonQuery(); return; }
private void PopulateOccupation() { SqlConnection sqlConnectionX; SqlCommand sqlCommandX; SqlParameter sqlParam; SqlDataReader sqlDR; try { sqlConnectionX = new SqlConnection(ConfigurationManager.AppSettings["SQLConnection"]); sqlConnectionX.Open(); sqlCommandX = new SqlCommand(); sqlCommandX.Connection = sqlConnectionX; sqlCommandX.CommandType = CommandType.StoredProcedure; sqlCommandX.CommandText = "spx_SELECT_Occupation"; sqlDR = sqlCommandX.ExecuteReader(); DataTable dtResult = new DataTable("Result"); dtResult.Load(sqlDR); sqlDR.Close(); sqlCommandX.Cancel(); sqlCommandX.Dispose(); RadComboBoxOccupation.DataTextField = "Occupation"; RadComboBoxOccupation.DataValueField = "OccupationID"; RadComboBoxItem cbDefaultItem = new RadComboBoxItem(); cbDefaultItem.Value = "0"; cbDefaultItem.Text = "- Please select an occupation -"; RadComboBoxOccupation.Items.Add(cbDefaultItem); foreach (DataRow dataRow in dtResult.Rows) { RadComboBoxItem cbItem = new RadComboBoxItem(); cbItem.Value = dataRow[0].ToString().Trim(); cbItem.Text = dataRow[1].ToString().Trim(); cbItem.Attributes.Add("Life", dataRow[2].ToString()); cbItem.Attributes.Add("ADW", dataRow[3].ToString()); cbItem.Attributes.Add("OCC", dataRow[4].ToString()); RadComboBoxOccupation.Items.Add(cbItem); } //RadComboBoxOccupation.DataSource = dtResult; //RadComboBoxOccupation.DataBind(); } catch (Exception ex) { lblInfo.Text = ex.Message; lblInfo2.Text = ex.Message; } }
protected void Page_Load(object sender, EventArgs e) { if (Session["USER_TYPE"] == null) { Response.Redirect("Logout.aspx"); } if (Page.IsPostBack == false) { if (Session["USER_TYPE"].ToString() == "A") { Panel2.Visible = false; } else { Panel3.Visible = false; } try { string connStr = ConfigurationManager.ConnectionStrings["LibraryConn"].ToString(); SqlConnection conn = new SqlConnection(connStr); /*string sql = "SELECT DISTINCT BT.BOOK_ID, BM.BOOK_TITLE, A.AUTHOR_NAME, P.PUBLISHER_NAME, P.PUBLISHING_YEAR, P.PUBLISHING_HOUSE, P.PUBLISHER_EDITION, C.CATEGORY_NAME, "; sql = sql + "S.SUPPLIER_NAME, BT.ISSUE_DATE, BT.RETURN_DATE, BT.ACTUAL_RETURN_DATE, BT.LATE_FEE FROM BOOK_TRANSACTION BT INNER JOIN BOOK_MASTER BM ON BT.BOOK_ID = BM.BOOK_ID "; sql = sql + "INNER JOIN AUTHOR A ON BM.AUTHOR_ID = A.AUTHOR_ID INNER JOIN PUBLISHER P ON BM.PUBLISHER_ID = P.PUBLISHER_ID INNER JOIN CATEGORY C ON BM.CATEGORY_ID = C.CATEGORY_ID "; sql = sql + "INNER JOIN SUPPLIER S ON BM.SUPPLIER_ID = S.SUPPLIER_ID WHERE BT.MEMBER_ID = 'M0002' ORDER BY BT.BOOK_ID";*/ string sql = "SELECT DISTINCT BT.BOOK_ID AS 'Book ID', BM.BOOK_TITLE AS 'Book Tite' , A.AUTHOR_NAME AS 'Author Name', "; sql = sql + "P.PUBLISHER_NAME AS 'Publisher Name', BT.ISSUE_DATE AS 'Issue Date', BT.RETURN_DATE AS 'Return Date', "; sql = sql + "BT.ACTUAL_RETURN_DATE AS 'Actual Return Date', BT.LATE_FEE AS 'Late Fee' FROM BOOK_TRANSACTION BT INNER JOIN "; sql = sql + "BOOK_MASTER BM ON BT.BOOK_ID = BM.BOOK_ID INNER JOIN AUTHOR A ON BM.AUTHOR_ID = A.AUTHOR_ID "; sql = sql + "INNER JOIN PUBLISHER P ON BM.PUBLISHER_ID = P.PUBLISHER_ID WHERE BT.MEMBER_ID = '" + Session["MEMBER_ID"].ToString() + "' ORDER BY BT.BOOK_ID"; SqlCommand cmd = new SqlCommand(sql, conn); SqlDataAdapter adp = new SqlDataAdapter(cmd); //Fill Dataet DataSet ds = new DataSet(); adp.Fill(ds, "Book_Transaction"); gvBook.DataSource = ds; gvBook.DataBind(); adp.Dispose(); cmd.Cancel(); conn.Close(); } catch (Exception ex) { throw ex; } } }
public void executeSql(string sql) { try { SqlCommand sqlCommand = new SqlCommand(sql, this.GetSqlConnection()); sqlCommand.ExecuteNonQuery(); sqlCommand.Cancel(); } catch (Exception ex) { MessageBox.Show(ex.Message); } }
public static List<Account> GetAccount() { List<Account> AccountList = new List<Account>(); SqlDataReader dr = null; const string storedProcedureName = "GetAccount"; using (SqlConnection connection = new SqlConnection(Reuasbles.GetConnectionString())) { SqlCommand command = new SqlCommand(storedProcedureName, connection); command.CommandType = CommandType.StoredProcedure; try { connection.Open(); dr = command.ExecuteReader(); Account acct; while (dr.Read()) { acct = new Account(); //acct.id = (int)dr["id"]; //acct.balance = (int)dr["balance"]; //acct.trn = (int)dr["trn"]; //acct.acc_type = dr["acc_type"].ToString(); ////acct.date_created = dr["mname"].ToString(); AccountList.Add(acct); } dr.Close(); command.Cancel(); } catch (Exception err) { err.Data["Procedure"] = "Getting list of Clients"; err.Data["sp"] = storedProcedureName; } finally { connection.Close(); } } return AccountList; }
protected void Button1_Click(object sender, EventArgs e) { // Show資料用的 SqlConnection Conn = new SqlConnection(); Conn.ConnectionString = WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; //SqlConnection Conn = new SqlConnection(WebConfigurationManager.ConnectionStrings("testConnectionString").ConnectionString.ToString()); SqlDataReader dr = null; SqlCommand cmd = new SqlCommand("SELECT id, Order_date, Assign_numbers, Account_name, Income, Spend FROM [Account_Order_M_View] where Account_name like '%" + TextBox1.Text + "%'", Conn); try //==== 以下程式,只放「執行期間」的指令!===================== { Conn.Open(); //---- 這時候才連結DB dr = cmd.ExecuteReader(); //---- 這時候執行SQL指令,取出資料 //string myTitle, mySummary; myTable.Rows.Add(BuildNewRow("Id", "Order_date", "Assign_numbers", "Account_name", "Income", "Spend")); while (dr.Read()) { //myTitle = "<Strong><B><A href=Disp.aspx?id=" + dr["id"] + ">" + dr["title"] + "</A></B></Strong>"; //mySummary = "<small><font color=#969696>" + dr["summary"] + "</font></small>......<A href=Disp.aspx?id=" + dr["id"] + ">詳見內文</A>"; myTable.Rows.Add(BuildNewRow(dr["Id"].ToString(), dr["Order_date"].ToString(), dr["Assign_numbers"].ToString(), dr["Account_name"].ToString(), dr["Income"].ToString(), dr["Spend"].ToString())); //Table1.Rows.Add(BuildNewRow("", mySummary)); } } catch (Exception ex) //---- 如果程式有錯誤或是例外狀況,將執行這一段 { Response.Write("<b>Error Message---- </b>" + ex.ToString()); } finally { //---- Always call Close when done reading. if (dr != null) { cmd.Cancel(); dr.Close(); } //---- Close the connection when done with it. if (Conn.State == ConnectionState.Open) { Conn.Close(); Conn.Dispose(); //---- 一開始宣告有用到 New的,最後必須以 .Dispose()結束 } } }
public static List<Car> GetCar() { List<Car> CarList = new List<Car>(); SqlDataReader dr = null; const string storedProcedureName = "sp_GetCars"; using (SqlConnection connection = new SqlConnection(Reuasbles.GetConnectionString())) { SqlCommand command = new SqlCommand(storedProcedureName, connection); command.CommandType = CommandType.StoredProcedure; try { connection.Open(); dr = command.ExecuteReader(); Car car; while (dr.Read()) { car = new Car(); car.chas = dr["chassi_no"].ToString(); car.colour = dr["colour"].ToString(); car.carmake = dr["carmake"].ToString(); CarList.Add(car); } dr.Close(); command.Cancel(); } catch (Exception err) { err.Data["Procedure"] = "Getting list of Cars"; err.Data["sp"] = storedProcedureName; } finally { connection.Close(); } } return CarList; }
public SqlDataReader executeRetornaSql(string sql) { try { SqlCommand sqlCommand = new SqlCommand(sql, this.GetSqlConnection()); SqlDataReader reader = sqlCommand.ExecuteReader(); sqlCommand.Cancel(); return reader; } catch (Exception ex) { MessageBox.Show(ex.Message); return null; } }
public static DataTable GetDataTable(string strSQL, List<Parameter> Pars) { DataTable dt = new DataTable(); try { Open(); SqlCommand sqlCand = new SqlCommand(strSQL, SqlConn); if ((Pars != null) && (Pars.Count > 0)) { foreach (Parameter par in Pars) { SqlParameter spar = new SqlParameter(); spar.Value = par.pvalues; spar.ParameterName = par.pname; sqlCand.Parameters.Add(spar); } } SqlDataAdapter sqlda = new SqlDataAdapter(sqlCand); sqlCand.ExecuteNonQuery(); sqlda.Fill(dt); sqlCand.Cancel(); } catch (Exception ex) { // flog_Class.WriteFlog(strSQL + "\r\n" + ex.Message); } // Close(); try { for (int i = 0; i < dt.Columns.Count; i++) { dt.Columns[i].ColumnName = dt.Columns[i].ColumnName.ToLower().Trim(); } } catch (Exception ex) { //string str4 = strSQL + "\r\n" + ex.Message; //flog_Class.WriteFlog(str4); } return dt; }
public int GetSangamDashboardData(string strConnectionString, string strSangamID, ref Core.Sangam.SangamDashboardEntity objSangamDashboardEntity) { try { using (SqlConnection objSqlConnection = new SqlConnection(strConnectionString)) { objSqlConnection.Open(); // 1. create a command object identifying the stored procedure SqlCommand objSqlCommand = new SqlCommand("UspGetSangamProfilesInfo", objSqlConnection); // 2. set the command object so it knows to execute a stored procedure objSqlCommand.CommandType = CommandType.StoredProcedure; // 3. add parameter to command, which will be passed to the stored procedure objSqlCommand.Parameters.Add(new SqlParameter("@SangamID", strSangamID)); // execute the command using (SqlDataReader objSqlDataReader = objSqlCommand.ExecuteReader()) { while (objSqlDataReader.Read()) { if (!string.IsNullOrEmpty(objSqlDataReader["LoggedInCount"].ToString())) objSangamDashboardEntity.TotalLogin = Convert.ToInt32(objSqlDataReader["LoggedInCount"].ToString()); if (!string.IsNullOrEmpty(objSqlDataReader["ViewedProfile"].ToString())) objSangamDashboardEntity.ProfilesViewed = Convert.ToInt32(objSqlDataReader["ViewedProfile"].ToString()); if (!string.IsNullOrEmpty(objSqlDataReader["ActiveProfiles"].ToString())) objSangamDashboardEntity.ActiveProfiles = Convert.ToInt32(objSqlDataReader["ActiveProfiles"].ToString()); if (!string.IsNullOrEmpty(objSqlDataReader["TotalProfiles"].ToString())) objSangamDashboardEntity.TotalProfiles = Convert.ToInt32(objSqlDataReader["TotalProfiles"].ToString()); } objSqlDataReader.Close(); } objSqlCommand.Cancel(); objSqlCommand.Dispose(); objSqlConnection.Close(); objSqlConnection.Dispose(); } } catch (Exception objEx) { Helpers.LogExceptionInFlatFile(objEx); } return 0; }
public void insertFOB(string orderNumber, decimal truckFob) { using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CN_INSIDER"].ConnectionString)) { System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(); //check for duplicates String query = "Insert into TruckEntry (SalesOrderNumber,ML_UDF_SOH_OUTBOUND_SHIP) values(@SalesOrderNumber,@truckFOB)"; cmd.CommandText = query; cmd.CommandType = System.Data.CommandType.Text; cmd.Connection = conn; conn.Open(); cmd.Parameters.AddWithValue("@truckFOB", truckFob); cmd.Parameters.AddWithValue("@SalesOrderNumber", orderNumber); cmd.ExecuteNonQuery(); cmd.Cancel(); cmd.Dispose(); conn.Close(); } }
protected void Button1_Click(object sender, EventArgs e) { // Show資料用的 SqlConnection Conn = new SqlConnection(); Conn.ConnectionString = WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; SqlDataReader dr = null; SqlCommand cmd = new SqlCommand("select ID, Order_date, Assign_numbers, Account_numbers, Account_name, Account_abstract from [Account_Order_M_View] where Assign_numbers like @Assign_numbers order by Order_date,ID desc", Conn); cmd.Parameters.AddWithValue("@Assign_numbers", TextBox1.Text.Trim() + "%"); try { Conn.Open(); dr = cmd.ExecuteReader(); myTable.Rows.Add(BuildNewRow("Order_date", "Assign_numbers", "Account_name", "Account_abstract","")); string Lotto; while (dr.Read()) { Lotto = "<b><a href=LottoReceiptUpdate.aspx?id="+ dr["Id"]+">選擇</a></b>"; myTable.Rows.Add(BuildNewRow(Convert.ToDateTime(dr["Order_date"]).ToShortDateString(), dr["Assign_numbers"].ToString(), dr["Account_name"].ToString(), dr["Account_abstract"].ToString(),Lotto)); } } catch (Exception ex) { Response.Write("<b>Error Message---- </b>" + ex.ToString()); } finally { if (dr != null) { cmd.Cancel(); dr.Close(); } if (Conn.State == ConnectionState.Open) { Conn.Close(); Conn.Dispose(); } } }
public List <string> providerId;//= new List<string>(); protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { providerId = new List <string>(); string st = "", dist = "", cit = ""; cn.con.Open(); string qry = "Select state,district, city_village from customer where c_id=@cid"; cn.cmd = new System.Data.SqlClient.SqlCommand(qry, cn.con); try { cn.cmd.Parameters.AddWithValue("@cid", Session["c_id"].ToString()); } catch { Response.Redirect("CustomerLogin.aspx"); } cn.rdr = cn.cmd.ExecuteReader(); if (cn.rdr.Read()) { st = cn.rdr.GetString(0); dist = cn.rdr.GetString(1); cit = cn.rdr.GetString(2); } cn.rdr.Close(); string qry2 = "Select p_id from provider where state= @st and district=@ds and city_village=@cty"; SqlCommand mycmd = new System.Data.SqlClient.SqlCommand(qry2, cn.con); mycmd.Parameters.AddWithValue("@st", st); mycmd.Parameters.AddWithValue("@ds", dist); mycmd.Parameters.AddWithValue("@cty", cit); SqlDataReader myrdr = mycmd.ExecuteReader(); while (myrdr.Read()) { providerId.Add(myrdr.GetString(0)); } myrdr.Close(); mycmd.Cancel(); cn.con.Close(); } }
public VO.usuario validar(String usuario, String password) { VO.usuario Obusuario = null; using (SqlConnection con = conexion.obtenerConexion()) { SqlCommand comando = new SqlCommand(string.Format("select * from usuario where usuario='{0}' and clave= '{1}' ", usuario, password), con); SqlDataReader reader = comando.ExecuteReader(); if (reader.Read()) { Obusuario = new VO.usuario(reader.GetString(0),reader.GetString(1),reader.GetString(2),reader.GetString(3)); } comando.Cancel(); con.Close(); } return Obusuario; }
protected void Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack) { SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString.ToString()); SqlDataReader dr = null; SqlCommand cmd = new SqlCommand("select * from [Account_Order_M_View] where id=" + Request["Id"], conn); try { conn.Open(); dr = cmd.ExecuteReader(); dr.Read(); Label1.Text = Convert.ToDateTime(dr["Order_date"]).ToShortDateString(); Label2.Text = dr["Assign_numbers"].ToString(); Label3.Text = dr["Account_numbers"].ToString(); Label4.Text = dr["Account_name"].ToString(); Label5.Text = dr["Account_abstract"].ToString(); Label6.Text = dr["Income"].ToString(); Label7.Text = dr["Spend"].ToString(); } catch (Exception ex) { Response.Write("error" + ex.ToString()); } finally { if (dr != null) { cmd.Cancel(); dr.Close(); } if (conn.State == ConnectionState.Open) { conn.Close(); conn.Dispose(); } } } }
protected void Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack) { //月份用 SqlConnection Conn_Month = new SqlConnection(); Conn_Month.ConnectionString = WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; SqlDataReader dr_Month = null; SqlCommand cmd_Month = new SqlCommand("SELECT * FROM [Account_Numbers_D]", Conn_Month); try { Conn_Month.Open(); dr_Month = cmd_Month.ExecuteReader(); DropDownList1.DataValueField = "Account_numbers"; DropDownList1.DataTextField = "Account_name"; DropDownList1.DataSource = dr_Month; DropDownList1.DataBind(); } catch (Exception ex_Month) { Response.Write("<b>Error Message---- </b>" + ex_Month.ToString()); } finally { if (dr_Month != null) { cmd_Month.Cancel(); dr_Month.Close(); } if (Conn_Month.State == ConnectionState.Open) { Conn_Month.Close(); Conn_Month.Dispose(); } } //月份用 } }
protected void Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack) { SqlConnection Conn_Month = new SqlConnection(); Conn_Month.ConnectionString = WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; SqlDataReader dr_Month = null; SqlCommand cmd_Month = new SqlCommand("SELECT DISTINCT LEFT (Order_date, 7) AS Expr1 FROM Account_Order_M_View", Conn_Month); try { Conn_Month.Open(); dr_Month = cmd_Month.ExecuteReader(); DropDownList1.DataValueField = "Expr1"; //沒有指定名稱直接沿用expr1 DropDownList1.DataTextField = "Expr1"; //沒有指定名稱直接沿用expr1 DropDownList1.DataSource = dr_Month; DropDownList1.DataBind(); DropDownList1.Items.Insert(0, "-- Please select One --"); } catch (Exception ex_Month) { Response.Write("<b>Error Message---- </b>" + ex_Month.ToString()); } finally { if (dr_Month != null) { cmd_Month.Cancel(); dr_Month.Close(); } if (Conn_Month.State == ConnectionState.Open) { Conn_Month.Close(); Conn_Month.Dispose(); } } } }
public void checkFOB(string orderNumber, decimal truckFob) { String check = ""; using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CN_INSIDER"].ConnectionString)) { System.Data.SqlClient.SqlDataReader reader = null; System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(); //check for duplicates String query = "SELECT SALESORDERNumber,ML_UDF_SOH_OUTBOUND_SHIP From TruckEntry where SALESORDERNumber =@SalesOrderNumber"; cmd.CommandText = query; cmd.CommandType = System.Data.CommandType.Text; cmd.Connection = conn; conn.Open(); cmd.Parameters.AddWithValue("@SalesOrderNumber", orderNumber); Console.Write("Executing Reader"); reader = cmd.ExecuteReader(); while (reader.Read()) { if (reader["SALESORDERNumber"].ToString() != null) { check = reader["SALESORDERNumber"].ToString(); } } cmd.Cancel(); cmd.Dispose(); conn.Close(); } if (check != "") { updateFOB(orderNumber, truckFob); } else { insertFOB(orderNumber, truckFob); } }
protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e) { using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(clsRPlus.sCN_SALES)) { try { if (e.CommandName.Equals("New")) { LinkButton btnNew = e.CommandSource as LinkButton; GridViewRow row = btnNew.NamingContainer as GridViewRow; //System.Data.SqlClient.SqlDataReader reader = null; System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(); if (row == null) { return; } TextBox txtforecastYear = GridView1.FooterRow.FindControl("forecastYearTextBoxNew") as TextBox; DropDownList txtforecastCategory = GridView1.FooterRow.FindControl("forecastCategoryTextBoxEmpty") as DropDownList; TextBox txtJan = GridView1.FooterRow.FindControl("JanTextBoxNew") as TextBox; TextBox txtFeb = GridView1.FooterRow.FindControl("FebTextBoxNew") as TextBox; TextBox txtMar = GridView1.FooterRow.FindControl("MarTextBoxNew") as TextBox; TextBox txtApr = GridView1.FooterRow.FindControl("AprTextBoxNew") as TextBox; TextBox txtMay = GridView1.FooterRow.FindControl("MayTextBoxNew") as TextBox; TextBox txtJun = GridView1.FooterRow.FindControl("JunTextBoxNew") as TextBox; TextBox txtJul = GridView1.FooterRow.FindControl("JulTextBoxNew") as TextBox; TextBox txtAug = GridView1.FooterRow.FindControl("AugTextBoxNew") as TextBox; TextBox txtSep = GridView1.FooterRow.FindControl("SepTextBoxNew") as TextBox; TextBox txtOct = GridView1.FooterRow.FindControl("OctTextBoxNew") as TextBox; TextBox txtNov = GridView1.FooterRow.FindControl("NovTextBoxNew") as TextBox; TextBox txtDec = GridView1.FooterRow.FindControl("DecTextBoxNew") as TextBox; String query = "INSERT INTO [SkupForecasts] ( [forecastYear], [forecastCategory], [Jan], [Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec]) VALUES (@forecastYear, @forecastCategory, @Jan, @Feb, @Mar, @Apr, @May, @Jun, @Jul, @Aug, @Sep, @Oct, @Nov, @Dec)"; cmd.CommandText = query; cmd.CommandType = System.Data.CommandType.Text; cmd.Connection = conn; cmd.Parameters.AddWithValue("forecastYear", txtforecastYear.Text); cmd.Parameters.AddWithValue("forecastCategory", txtforecastCategory.Text); cmd.Parameters.AddWithValue("Jan", txtJan.Text); cmd.Parameters.AddWithValue("Feb", txtFeb.Text); cmd.Parameters.AddWithValue("Mar", txtMar.Text); cmd.Parameters.AddWithValue("Apr", txtApr.Text); cmd.Parameters.AddWithValue("May", txtMay.Text); cmd.Parameters.AddWithValue("Jun", txtJun.Text); cmd.Parameters.AddWithValue("Jul", txtJul.Text); cmd.Parameters.AddWithValue("Aug", txtAug.Text); cmd.Parameters.AddWithValue("Sep", txtSep.Text); cmd.Parameters.AddWithValue("Oct", txtOct.Text); cmd.Parameters.AddWithValue("Nov", txtNov.Text); cmd.Parameters.AddWithValue("Dec", txtDec.Text); conn.Open(); cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); cmd.Cancel(); cmd.Dispose(); conn.Close(); Response.AppendHeader("Refresh", "0,URL="); } if (e.CommandName.Equals("EmptyNew")) { LinkButton btnNew = e.CommandSource as LinkButton; GridViewRow row = btnNew.NamingContainer as GridViewRow; System.Data.SqlClient.SqlDataReader reader = null; System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(); if (row == null) { return; } TextBox txtforecastYear = GridView1.Controls[0].Controls[0].FindControl("forecastYearTextBoxEmpty") as TextBox; DropDownList txtforecastCategory = GridView1.Controls[0].Controls[0].FindControl("forecastCategoryTextBoxEmpty") as DropDownList; TextBox txtJan = GridView1.Controls[0].Controls[0].FindControl("JanTextBoxEmpty") as TextBox; TextBox txtFeb = GridView1.Controls[0].Controls[0].FindControl("FebTextBoxEmpty") as TextBox; TextBox txtMar = GridView1.Controls[0].Controls[0].FindControl("MarTextBoxEmpty") as TextBox; TextBox txtApr = GridView1.Controls[0].Controls[0].FindControl("AprTextBoxEmpty") as TextBox; TextBox txtMay = GridView1.Controls[0].Controls[0].FindControl("MayTextBoxEmpty") as TextBox; TextBox txtJun = GridView1.Controls[0].Controls[0].FindControl("JunTextBoxEmpty") as TextBox; TextBox txtJul = GridView1.Controls[0].Controls[0].FindControl("JulTextBoxEmpty") as TextBox; TextBox txtAug = GridView1.Controls[0].Controls[0].FindControl("AugTextBoxEmpty") as TextBox; TextBox txtSep = GridView1.Controls[0].Controls[0].FindControl("SepTextBoxEmpty") as TextBox; TextBox txtOct = GridView1.Controls[0].Controls[0].FindControl("OctTextBoxEmpty") as TextBox; TextBox txtNov = GridView1.Controls[0].Controls[0].FindControl("NovTextBoxEmpty") as TextBox; TextBox txtDec = GridView1.Controls[0].Controls[0].FindControl("DecTextBoxEmpty") as TextBox; String query = "INSERT INTO [SkupForecasts] ( [forecastYear], [forecastCategory], [Jan], [Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec]) VALUES (@forecastYear, @forecastCategory, @Jan, @Feb, @Mar, @Apr, @May, @Jun, @Jul, @Aug, @Sep, @Oct, @Nov, @Dec)"; cmd.CommandText = query; cmd.CommandType = System.Data.CommandType.Text; cmd.Connection = conn; cmd.Parameters.AddWithValue("forecastYear", txtforecastYear.Text); cmd.Parameters.AddWithValue("forecastCategory", txtforecastCategory.Text); cmd.Parameters.AddWithValue("Jan", txtJan.Text); cmd.Parameters.AddWithValue("Feb", txtFeb.Text); cmd.Parameters.AddWithValue("Mar", txtMar.Text); cmd.Parameters.AddWithValue("Apr", txtApr.Text); cmd.Parameters.AddWithValue("May", txtMay.Text); cmd.Parameters.AddWithValue("Jun", txtJun.Text); cmd.Parameters.AddWithValue("Jul", txtJul.Text); cmd.Parameters.AddWithValue("Aug", txtAug.Text); cmd.Parameters.AddWithValue("Sep", txtSep.Text); cmd.Parameters.AddWithValue("Oct", txtOct.Text); cmd.Parameters.AddWithValue("Nov", txtNov.Text); cmd.Parameters.AddWithValue("Dec", txtDec.Text); conn.Open(); cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); cmd.Cancel(); cmd.Dispose(); conn.Close(); Response.AppendHeader("Refresh", "0,URL="); } } catch (Exception ex) { } finally { conn.Close(); } } }
/// <summary> /// Executes an SQL query and populates a given <see cref="QueryResults" /> instance with the results. /// </summary> /// <param name="results"><see cref="QueryResults" /> instance to populate with results.</param> /// <param name="command">SQL command to execute.</param> /// <param name="result"><see cref="AsyncResult"/> instance to use to mark state changes.</param> /// <param name="messages"><see cref="StringBuilder" /> instance to which to append messages.</param> /// <param name="IncludeExecutionPlan">If true indciates that the query execution plans are expected to be contained /// in the results sets; otherwise, false.</param> private static void PopulateResults(QueryResults results, SqlCommand command, AsyncQueryRunner.AsyncResult result, StringBuilder messages, bool IncludeExecutionPlan) { QueryPlan plan = new QueryPlan(); using (SqlDataReader reader = command.ExecuteReader()) { if (result != null && reader.HasRows) { result.HasOutput = true; } do { // Check to see if the resultset is an execution plan if (IncludeExecutionPlan && reader.FieldCount == 1 && reader.GetName(0) == "Microsoft SQL Server 2005 XML Showplan") { if (reader.Read()) { plan.AppendStatementPlan(reader[0].ToString()); } } else { if (reader.FieldCount == 0) { if (reader.RecordsAffected >= 0) { messages.AppendFormat("({0} row(s) affected)\n\n", reader.RecordsAffected); } continue; } var resultSet = new ResultSet(); resultSet.MessagePosition = messages.Length; results.ResultSets.Add(resultSet); for (int i = 0; i < reader.FieldCount; i++) { var columnInfo = new ResultColumnInfo(); columnInfo.Name = reader.GetName(i); ResultColumnType colType; if (ResultColumnInfo.ColumnTypeMap.TryGetValue(reader.GetFieldType(i), out colType)) { columnInfo.Type = colType; } resultSet.Columns.Add(columnInfo); } int currentRow = 0; while (reader.Read()) { if (currentRow++ >= MAX_RESULTS) { results.Truncated = true; results.MaxResults = MAX_RESULTS; break; } var row = new List<object>(); resultSet.Rows.Add(row); for (int i = 0; i < reader.FieldCount; i++) { object col = reader.GetValue(i); if (col is DateTime) { var date = (DateTime)col; col = date.ToJavascriptTime(); } row.Add(col); } } if (results.Truncated) { // next result would force ado.net to fast forward // through the result set, which is way too slow break; } if (reader.RecordsAffected >= 0) { messages.AppendFormat("({0} row(s) affected)\n\n", reader.RecordsAffected); } messages.AppendFormat("({0} row(s) affected)\n\n", resultSet.Rows.Count); } } while (reader.NextResult()); command.Cancel(); } results.ExecutionPlan = plan.PlanXml; }
protected void AddUser_Click(object sender, EventArgs e) { Register.Visible = true; Department.Visible = true; login.Visible = false; Nickname.Visible = true; AddUser.Visible = false; Label1.Visible = true; Department.Items.Add(new ListItem("請選擇您的部門")); using (SqlConnection conn = new SqlConnection(DBTools.ConnectionString)) { conn.Open(); using (SqlCommand cmd = new SqlCommand("SELECT DepartmentName,DepartmentID from Department", conn)) { using (SqlDataReader dr = cmd.ExecuteReader()) { while (dr.Read()) Department.Items.Add(new ListItem(dr.GetString(0), dr.GetByte(1).ToString())); } cmd.Cancel(); } } }
public string ExecuteSQLCommand(string queryString, string connectionString, int TimeOut=29) { string s = ""; int l = 32; if (l > queryString.Length) l = queryString.Length; try { lock (db_lock) using (SqlConnection connection =GetDBCon(connectionString)) { try { //connection.Open(); if(connection.State != ConnectionState.Closed) using (SqlCommand command = new SqlCommand(queryString, connection)) { try { if (command != null) { command.CommandTimeout = TimeOut; command.ExecuteNonQuery(); } } catch (Exception exx) { s = exx.Message; try { if (command != null) command.Cancel(); } catch (Exception exxx) { Console.WriteLine("Ошибка отмены запроса " + queryString.Substring(0, l) + " т.к. " + exxx.Message); } finally { Console.WriteLine(DateTime.Now.ToLongTimeString() + " Не могу выполнить команду2 " + queryString.Substring(0, l) + " т.к. " + exx.Message); } } /*finally { if (command != null) { command.Dispose(); GC.SuppressFinalize(command); } }*/ } } catch (Exception ex) { s = ex.Message; Console.WriteLine("Не могу выполнить команду1 " + queryString.Substring(0, l) + " т.к. " + ex.Message); } finally { if (connection != null) { if(connection.State!= ConnectionState.Closed) connection.Close(); connection.Dispose(); GC.SuppressFinalize(connection); } } } } catch (Exception ex) { s = ex.Message; Console.WriteLine("Не могу выполнить команду " + queryString.Substring(0, l) + " т.к. " + ex.Message); } return s; //lineCount; }
private static string[][] RedJobsSub(DataDBAsynchService ddbs) { //Console.WriteLine(DateTime.Now.ToLongTimeString() + " Начали получать расшифровку заданий"); List<string[]> jobs = null; string SQLstr = ""; try { SQLstr = //"begin tran tran1; "+ //"SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; "+ "select pj.ParserName, pj.id parser_id, am.[ID], am.[Address],am.[Sticker],[Encoding],[IpProxy],[PortProxy],[WebPreAuthentificationPage] " + ",[WebAuthentificationPage],[WebConnectionString],[Site],[Source],[PathForPhoto], DelayFrom, DelayTo, ap.id " + "from AddressesMask am with(xlock) " + "inner join PjobStat pj " + "on pj.ID=am.JobID " //+ "inner join AddressesParsing ap with(xlock) " + "inner join AddressesParsingStart ap " + "with(xlock) on ap.Address_ref=am.id " + " where ap.id not in(select AddressStart_Ref from AddressesParsingFinish) and ap.Host_Ref= " + GetHostRef(ddbs); //+"; commit tran tran1;"; //+ " order by pj.ParserName"; lock(DataDBAsynchService.db_lock) using(SqlConnection sc =ddbs.GetDBCon(ddbs.ConnectionString)) try { using(SqlCommand SQLCmd = new SqlCommand(SQLstr, sc)) try { SQLCmd.CommandTimeout = 29; using (SqlDataReader MydataReader2 = SQLCmd.ExecuteReader()) try { if (MydataReader2 != null) if (MydataReader2.HasRows) try { jobs = new List<string[]>(); object o0 = ""; //if (MydataReader2.HasRows) while (MydataReader2.Read()) { string[] job = new string[17]; //Console.WriteLine(DateTime.Now.ToLongTimeString() + " Считали строку расшифровки"); try { if (MydataReader2.FieldCount == 17) { if (MydataReader2.IsDBNull(0)) o0 = ""; else o0 = MydataReader2.GetSqlString(0).ToString(); /*Array.Resize(ref job, 1);*/ job[0] = (string)o0; //[ID] ///*Array.Resize(ref job, 1);*/ job[0] = "70"; if (MydataReader2.IsDBNull(1)) o0 = "0"; else o0 = MydataReader2.GetInt32(1).ToString(); /*Array.Resize(ref job, 2);*/ job[1] = (string)o0;//[ParserJobID] ///*Array.Resize(ref job, 2);*/ job[1] = "GilcomGotovoeDataCollector"; if (MydataReader2.IsDBNull(2)) o0 = "0"; else o0 = MydataReader2.GetInt32(2).ToString(); /*Array.Resize(ref job, 3);*/ job[2] = (string)o0;//[AddressMaskID] ///*Array.Resize(ref job, 2);*/ job[1] = "GilcomGotovoeDataCollector"; if (MydataReader2.IsDBNull(3)) o0 = ""; else o0 = MydataReader2.GetSqlString(3).ToString(); /*Array.Resize(ref job, 4);*/ job[3] = (string)o0;//[Encoding] ///*Array.Resize(ref job, 3);*/ job[2] = "UTF8"; if (MydataReader2.IsDBNull(4)) o0 = ""; else o0 = MydataReader2.GetSqlString(4).ToString(); /*Array.Resize(ref job, 5);*/ job[4] = (string)o0; if (MydataReader2.IsDBNull(5)) o0 = ""; else o0 = MydataReader2.GetSqlString(5).ToString(); /*Array.Resize(ref job, 6);*/ job[5] = (string)o0; if (MydataReader2.IsDBNull(6)) o0 = ""; else o0 = MydataReader2.GetSqlString(6).ToString(); /*Array.Resize(ref job, 7);*/ job[6] = (string)o0; if (MydataReader2.IsDBNull(7)) o0 = "0"; else o0 = MydataReader2.GetInt32(7).ToString(); /*Array.Resize(ref job, 8);*/ job[7] = (string)o0; if (MydataReader2.IsDBNull(8)) o0 = ""; else o0 = MydataReader2.GetSqlString(8).ToString(); /*Array.Resize(ref job, 9);*/ job[8] = (string)o0; if (MydataReader2.IsDBNull(9)) o0 = ""; else o0 = MydataReader2.GetSqlString(9).ToString(); /*Array.Resize(ref job, 10);*/ job[9] = (string)o0; if (MydataReader2.IsDBNull(10)) o0 = ""; else o0 = MydataReader2.GetSqlString(10).ToString(); /*Array.Resize(ref job, 11);*/ job[10] = (string)o0; if (MydataReader2.IsDBNull(11)) o0 = ""; else o0 = MydataReader2.GetSqlString(11).ToString(); /*Array.Resize(ref job, 12);*/ job[11] = (string)o0; if (MydataReader2.IsDBNull(12)) o0 = ""; else o0 = MydataReader2.GetSqlString(12).ToString(); /*Array.Resize(ref job, 13);*/ job[12] = (string)o0; if (MydataReader2.IsDBNull(13)) o0 = ""; else o0 = MydataReader2.GetSqlString(13).ToString(); /*Array.Resize(ref job, 14);*/ job[13] = (string)o0; if (MydataReader2.IsDBNull(14)) o0 = "0"; else o0 = MydataReader2.GetInt32(14).ToString(); /*Array.Resize(ref job, 15);*/ job[14] = (string)o0; if (MydataReader2.IsDBNull(15)) o0 = "0"; else o0 = MydataReader2.GetInt32(15).ToString(); /*Array.Resize(ref job, 16);*/ job[15] = (string)o0; if (MydataReader2.IsDBNull(16)) o0 = "0"; else o0 = MydataReader2.GetInt32(16).ToString(); /*Array.Resize(ref job, 17);*/ job[16] = (string)o0; /*if (MydataReader2.IsDBNull(14)) o0 = "0"; else o0 = MydataReader2.GetSqlString(14).ToString(); /*Array.Resize(ref job, 15);*/ //job[14] = (string)o0; //Array.Resize(ref jobs, jobs.Count() + 1); jobs.Add(job); } } catch (SystemException exx) { SQLCmd.Cancel(); if (o0 != null) { Console.WriteLine("Ошибка четния задания2. " + exx.Message + " получено " + o0.ToString()); ddbs.WriteErrorMessage(ddbs.ConnectionString, 0, null, "Ошибка четния задания2 " + SQLstr + ". " + exx.Message + " получено " + o0.ToString()); } else { Console.WriteLine("Ошибка четния задания2 " + exx.Message + " получено null"); ddbs.WriteErrorMessage(ddbs.ConnectionString, 0, null, "Ошибка четния задания2 " + exx.Message + " получено null"); } } finally { GC.SuppressFinalize(job); } } /*else { Console.WriteLine("Запрос вернул 0 строк. Вроде. Или HasRows косячит"); }*/ } catch (Exception ex) { SQLCmd.Cancel(); Console.WriteLine("Ошибка четния задания1 " + ex.Message); ddbs.WriteErrorMessage(ddbs.ConnectionString, 0, null, "Ошибка четния задания1 " + ex.Message); } } catch { } finally { if (MydataReader2 != null) { if (!MydataReader2.IsClosed) MydataReader2.Close(); MydataReader2.Dispose(); GC.SuppressFinalize(MydataReader2); } } } catch (Exception ex) { SQLCmd.Cancel(); Console.WriteLine("Ошибка четния задания3 " + ex.Message); //ddbs.WriteErrorMessage(ddbs.ConnectionString, 0, null, "Ошибка четния задания3 " + ex.Message); //return null; } finally { SQLCmd.Dispose(); GC.SuppressFinalize(SQLCmd); } } catch (Exception ex) { Console.WriteLine("Ошибка четния задания4 " + ex.Message); ddbs.WriteErrorMessage(ddbs.ConnectionString, 0, null, "Ошибка четния задания4 " + ex.Message); //return null; } finally { if (sc != null) { if(sc.State!= System.Data.ConnectionState.Closed) sc.Close(); sc.Dispose(); GC.SuppressFinalize(sc); } } } catch (SystemException ex) { ddbs.WriteErrorMessage(ddbs.ConnectionString, 0, null, "Ошибка четния задания " + ". " + ex.Message); //return null; } finally { //GC.Collect(); if (jobs!=null) Console.WriteLine(DateTime.Now.ToLongTimeString() + " Закончили получать задания. Получили: " + jobs.Count()); else Console.WriteLine(DateTime.Now.ToLongTimeString() + " Закончили получать задания. Но не получили("); } /*List<string[]> jobs_ret = new List<string[]>(jobs.Count()); for (int i = 0; i < 10; i++) for (int j = 0; j <= jobs.Count() / 10;j++ ) if (10 * j + i < jobs.Count()) jobs_ret.Add(jobs[10 * j + i]);*/ string[][] jobs_ = null; if (jobs != null) { if (jobs.Any()) { try { jobs_ = new string[jobs.Count][]; for (int i = 0; i < jobs.Count; i++) { jobs_[i] = jobs[i]; GC.SuppressFinalize(jobs[i]); } jobs.Clear(); } catch { Console.WriteLine("Произошла ошибка в финальной обработке заданий"); } finally { GC.SuppressFinalize(jobs); } } } return jobs_; }
public static int ExecuteSQLCount(string strSQL, List<Parameter> Pars) { int count = -1; try { Open(); SqlCommand sqlCand = new SqlCommand(strSQL, SqlConn); if ((Pars != null) && (Pars.Count > 0)) { foreach (Parameter par in Pars) { SqlParameter spar = new SqlParameter(); spar.Value = par.pvalues; spar.ParameterName = par.pname; sqlCand.Parameters.Add(spar); } } count = sqlCand.ExecuteNonQuery(); sqlCand.Cancel(); } catch (Exception ex) { //string str4 = strSQL + "\r\n" + ex.Message; //flog_Class.WriteFlog(str4); } Close(); return count; }
protected void editOrderButton_Click(object sender, EventArgs e) { using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CN_UPS"].ConnectionString)) { int loopCounter = 0; string checkString; System.Data.SqlClient.SqlDataReader reader = null; System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(); //check for duplicates String query = "SELECT * From Truck where Order_Number =@Order_Number"; cmd.CommandText = query; cmd.CommandType = System.Data.CommandType.Text; cmd.Connection = conn; conn.Open(); cmd.Parameters.AddWithValue("@Order_Number", messageLabel1.Text); Console.Write("Executing Reader"); reader = cmd.ExecuteReader(); while (reader.Read()) { loopCounter++; Order_Number.Text = (string)reader["Order_Number"]; checkString = (reader["Truck_CO"].ToString()); if (checkString != "") { int s3 = Truck_CO.Items.IndexOf(Truck_CO.Items.FindByValue(checkString)); Truck_CO.SelectedIndex = Truck_CO.Items.IndexOf(Truck_CO.Items.FindByValue(checkString)); //= Truck_CO.Items.IndexOf(Truck_CO.Items.FindByValue((reader["Truck_CO"].ToString()))); } ShipMonth.Text = (string)reader["ShipMonth"]; ShipDay.Text = "" + (string)reader["ShipDay"]; ShipYear.Text = "" + (string)reader["ShipYear"]; Tracking_number.Text = "" + (string)reader["Tracking_number"]; int check; if (loopCounter == 1) { checkString = reader["Packer"].ToString(); Packer1.SelectedIndex = Packer1.Items.IndexOf(Packer1.Items.FindByValue((reader["Packer"].ToString()))); linesPacker1.Text = reader["Lines"].ToString(); tagsPacker1.Text = reader["Tags"].ToString(); } else if (loopCounter == 2) { checkString = reader["Packer"].ToString(); Packer2.SelectedIndex = Packer2.Items.IndexOf(Packer2.Items.FindByValue((reader["Packer"].ToString()))); linesPacker2.Text = reader["Lines"].ToString(); tagsPacker2.Text = reader["Tags"].ToString(); } else if (loopCounter == 3) { checkString = reader["Packer"].ToString(); Packer3.SelectedIndex = Packer3.Items.IndexOf(Packer3.Items.FindByValue((reader["Packer"].ToString()))); linesPacker3.Text = reader["Lines"].ToString(); tagsPacker3.Text = reader["Tags"].ToString(); } else if (loopCounter == 4) { checkString = reader["Packer"].ToString(); Packer4.SelectedIndex = Packer4.Items.IndexOf(Packer4.Items.FindByValue((reader["Packer"].ToString()))); linesPacker4.Text = reader["Lines"].ToString(); tagsPacker4.Text = reader["Tags"].ToString(); } else if (loopCounter == 5) { checkString = reader["Packer"].ToString(); Packer5.SelectedIndex = Packer5.Items.IndexOf(Packer5.Items.FindByValue((reader["Packer"].ToString()))); linesPacker5.Text = reader["Lines"].ToString(); tagsPacker5.Text = reader["Tags"].ToString(); } else if (loopCounter == 6) { checkString = reader["Packer"].ToString(); Packer6.SelectedIndex = Packer6.Items.IndexOf(Packer6.Items.FindByValue((reader["Packer"].ToString()))); linesPacker6.Text = reader["Lines"].ToString(); tagsPacker6.Text = reader["Tags"].ToString(); } } reader.Close(); Order_Number.Enabled = false; cmd.Parameters.Clear(); messageLabel1.Visible = true; messageLabel2.Visible = true; messageLabel2.Style.Add("color", "Green"); messageLabel1.Text = Order_Number.Text; messageLabel2.Text = " being edited."; Update.Visible = true; Enter.Visible = false; editOrderButton.Visible = false; cmd.Cancel(); cmd.Dispose(); conn.Close(); } using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CN_INSIDER"].ConnectionString)) { System.Data.SqlClient.SqlDataReader reader = null; System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(); //check for duplicates String query = "SELECT ML_UDF_SOH_OUTBOUND_SHIP From TruckEntry where SALESORDERNumber =@SalesOrderNumber"; cmd.CommandText = query; cmd.CommandType = System.Data.CommandType.Text; cmd.Connection = conn; conn.Open(); cmd.Parameters.AddWithValue("@SalesOrderNumber", Order_Number.Text); Console.Write("Executing Reader"); reader = cmd.ExecuteReader(); while (reader.Read()) { if (reader["ML_UDF_SOH_OUTBOUND_SHIP"].ToString() != null) { truck_FOB.Text = reader["ML_UDF_SOH_OUTBOUND_SHIP"].ToString(); } } cmd.Cancel(); cmd.Dispose(); conn.Close(); } }
public static bool ExecuteSQL(string strSQL, string d_strSQL) { int count = -1; try { Open(); SqlCommand sqlCand = new SqlCommand(strSQL, SqlConn); // flog_Class.WriteFlog(strSQL + "\r\n"); count = sqlCand.ExecuteNonQuery(); sqlCand.Cancel(); return true; } catch (Exception ex) { //string str4 = strSQL + "\r\n" + ex.Message; //flog_Class.WriteFlog(str4); } Close(); return false; }
protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e) { using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CN_SALES"].ConnectionString)) { try { if (e.CommandName.Equals("New")) { LinkButton btnNew = e.CommandSource as LinkButton; GridViewRow row = btnNew.NamingContainer as GridViewRow; System.Data.SqlClient.SqlDataReader reader = null; System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(); if (row == null) { return; } TextBox txtItemCode = GridView1.FooterRow.FindControl("ItemCodeTextBoxNew") as TextBox; TextBox txtDate = GridView1.FooterRow.FindControl("transactiondateTextBoxNew") as TextBox; DropDownList txtTransactionType = GridView1.FooterRow.FindControl("TypeTextBoxEmpty") as DropDownList; TextBox txtValue = GridView1.FooterRow.FindControl("valueTextBoxNew") as TextBox; String query = "INSERT INTO [SkupFutureEntrys] ( [ItemCode], [transactiondate], [Type], [value]) VALUES (@ItemCode, @transactiondate, @Type, @value)"; cmd.CommandText = query; cmd.CommandType = System.Data.CommandType.Text; cmd.Connection = conn; cmd.Parameters.AddWithValue("ItemCode", txtItemCode.Text); cmd.Parameters.AddWithValue("transactiondate", txtDate.Text); cmd.Parameters.AddWithValue("Type", txtTransactionType.Text); cmd.Parameters.AddWithValue("value", txtValue.Text); conn.Open(); cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); cmd.Cancel(); cmd.Dispose(); conn.Close(); Response.AppendHeader("Refresh", "0,URL="); } if (e.CommandName.Equals("EmptyNew")) { LinkButton btnNew = e.CommandSource as LinkButton; GridViewRow row = btnNew.NamingContainer as GridViewRow; System.Data.SqlClient.SqlDataReader reader = null; System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(); if (row == null) { return; } TextBox txt = GridView1.Controls[0].Controls[0].FindControl("ItemCodeTextBoxEmpty") as TextBox; TextBox txtItemCode = GridView1.Controls[0].Controls[0].FindControl("ItemCodeTextBoxEmpty") as TextBox; TextBox txttransactiondate = GridView1.Controls[0].Controls[0].FindControl("transactiondateTextBoxEmpty") as TextBox; DropDownList txtTransactionType = GridView1.Controls[0].Controls[0].FindControl("TypeTextBoxEmpty") as DropDownList; TextBox txtValue = GridView1.Controls[0].Controls[0].FindControl("valueTextBoxEmpty") as TextBox; String query = "INSERT INTO [SkupFutureEntrys] ( [ItemCode], [transactiondate], [Type], [value]) VALUES (@ItemCode, @transactiondate, @Type, @value)"; cmd.CommandText = query; cmd.CommandType = System.Data.CommandType.Text; cmd.Connection = conn; cmd.Parameters.AddWithValue("ItemCode", txtItemCode.Text); cmd.Parameters.AddWithValue("transactiondate", txttransactiondate.Text); cmd.Parameters.AddWithValue("Type", txtTransactionType.Text); cmd.Parameters.AddWithValue("value", txtValue.Text); conn.Open(); cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); cmd.Cancel(); cmd.Dispose(); conn.Close(); Response.AppendHeader("Refresh", "0,URL="); } } catch (Exception ex) { } finally { conn.Close(); } } }
protected void Enter_Click(object sender, EventArgs e) { using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CN_UPS"].ConnectionString)) { String enteredOrderNumber = Order_Number.Text; System.Data.SqlClient.SqlDataReader reader = null; System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(); //check for duplicates String query = "SELECT * From Truck where Order_Number =@Order_Number"; cmd.CommandText = query; cmd.CommandType = System.Data.CommandType.Text; cmd.Connection = conn; conn.Open(); cmd.Parameters.AddWithValue("@Order_Number", enteredOrderNumber); Console.Write("Executing Reader"); reader = cmd.ExecuteReader(); int loopCounter = 1; while (reader.Read()) { orderNumber = (string)reader["Order_Number"]; Console.Write("{0,-25}", orderNumber); truckcompany = (string)reader["Truck_CO"]; month = (string)reader["ShipMonth"]; day = (string)reader["ShipDay"]; year = (string)reader["ShipYear"]; if (!reader["Tracking_number"].Equals(System.DBNull.Value)) { trackingNumber = (string)reader["Tracking_number"]; } } reader.Close(); cmd.Parameters.Clear(); if (orderNumber == enteredOrderNumber) { messageLabel1.Visible = true; messageLabel2.Visible = true; messageLabel2.Style.Add("color", "Red"); messageLabel1.Text = orderNumber; messageLabel2.Text = " is already in the system."; editOrderButton.Visible = true; clearForm(); } else//if there is nothing in datbase with this Order Number { query = "INSERT INTO Truck VALUES(@Order_Number,@Truck_CO,@ShipMonth,@ShipDay,@ShipYear,@Packer,@Lines,@Tags,@Tracking_number)"; cmd.CommandText = query; cmd.CommandType = System.Data.CommandType.Text; cmd.Connection = conn; String packer1string = Packer1.SelectedValue; if (Packer1.SelectedValue != "-- ") { cmd.Parameters.AddWithValue("@Order_Number", Order_Number.Text.Trim()); cmd.Parameters.AddWithValue("@Truck_CO", Truck_CO.Text.Trim()); cmd.Parameters.AddWithValue("@ShipMonth", ShipMonth.Text.Trim()); cmd.Parameters.AddWithValue("@ShipDay", ShipDay.Text.Trim()); cmd.Parameters.AddWithValue("@ShipYear", ShipYear.Text.Trim()); cmd.Parameters.AddWithValue("@Packer", Packer1.Text.Trim()); cmd.Parameters.AddWithValue("@Lines", linesPacker1.Text.Trim()); cmd.Parameters.AddWithValue("@Tags", tagsPacker1.Text.Trim()); cmd.Parameters.AddWithValue("@Tracking_number", Tracking_number.Text.Trim()); cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); } else { cmd.Parameters.AddWithValue("@Order_Number", Order_Number.Text.Trim()); cmd.Parameters.AddWithValue("@Truck_CO", Truck_CO.Text.Trim()); cmd.Parameters.AddWithValue("@ShipMonth", ShipMonth.Text.Trim()); cmd.Parameters.AddWithValue("@ShipDay", ShipDay.Text.Trim()); cmd.Parameters.AddWithValue("@ShipYear", ShipYear.Text.Trim()); cmd.Parameters.AddWithValue("@Packer", Packer1.Text.Trim()); cmd.Parameters.AddWithValue("@Lines", linesPacker1.Text.Trim()); cmd.Parameters.AddWithValue("@Tags", tagsPacker1.Text.Trim()); cmd.Parameters.AddWithValue("@Tracking_number", Tracking_number.Text.Trim()); cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); } if (Packer2.SelectedValue != "-- ") { cmd.Parameters.AddWithValue("@Order_Number", Order_Number.Text.Trim()); cmd.Parameters.AddWithValue("@Truck_CO", Truck_CO.Text.Trim()); cmd.Parameters.AddWithValue("@ShipMonth", ShipMonth.Text.Trim()); cmd.Parameters.AddWithValue("@ShipDay", ShipDay.Text.Trim()); cmd.Parameters.AddWithValue("@ShipYear", ShipYear.Text.Trim()); cmd.Parameters.AddWithValue("@Packer", Packer2.Text.Trim()); cmd.Parameters.AddWithValue("@Lines", linesPacker2.Text.Trim()); cmd.Parameters.AddWithValue("@Tags", tagsPacker2.Text.Trim()); cmd.Parameters.AddWithValue("@Tracking_number", Tracking_number.Text.Trim()); cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); } if (Packer3.SelectedValue != "-- ") { cmd.Parameters.AddWithValue("@Order_Number", Order_Number.Text.Trim()); cmd.Parameters.AddWithValue("@Truck_CO", Truck_CO.Text.Trim()); cmd.Parameters.AddWithValue("@ShipMonth", ShipMonth.Text.Trim()); cmd.Parameters.AddWithValue("@ShipDay", ShipDay.Text.Trim()); cmd.Parameters.AddWithValue("@ShipYear", ShipYear.Text.Trim()); cmd.Parameters.AddWithValue("@Packer", Packer3.Text.Trim()); cmd.Parameters.AddWithValue("@Lines", linesPacker3.Text.Trim()); cmd.Parameters.AddWithValue("@Tags", tagsPacker3.Text.Trim()); cmd.Parameters.AddWithValue("@Tracking_number", Tracking_number.Text.Trim()); cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); } if (Packer4.SelectedValue != "-- ") { cmd.Parameters.AddWithValue("@Order_Number", Order_Number.Text.Trim()); cmd.Parameters.AddWithValue("@Truck_CO", Truck_CO.Text.Trim()); cmd.Parameters.AddWithValue("@ShipMonth", ShipMonth.Text.Trim()); cmd.Parameters.AddWithValue("@ShipDay", ShipDay.Text.Trim()); cmd.Parameters.AddWithValue("@ShipYear", ShipYear.Text.Trim()); cmd.Parameters.AddWithValue("@Packer", Packer4.Text.Trim()); cmd.Parameters.AddWithValue("@Lines", linesPacker4.Text.Trim()); cmd.Parameters.AddWithValue("@Tags", tagsPacker4.Text.Trim()); cmd.Parameters.AddWithValue("@Tracking_number", Tracking_number.Text.Trim()); cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); } if (Packer5.SelectedValue != "-- ") { cmd.Parameters.AddWithValue("@Order_Number", Order_Number.Text.Trim()); cmd.Parameters.AddWithValue("@Truck_CO", Truck_CO.Text.Trim()); cmd.Parameters.AddWithValue("@ShipMonth", ShipMonth.Text.Trim()); cmd.Parameters.AddWithValue("@ShipDay", ShipDay.Text.Trim()); cmd.Parameters.AddWithValue("@ShipYear", ShipYear.Text.Trim()); cmd.Parameters.AddWithValue("@Packer", Packer5.Text.Trim()); cmd.Parameters.AddWithValue("@Lines", linesPacker5.Text.Trim()); cmd.Parameters.AddWithValue("@Tags", tagsPacker5.Text.Trim()); cmd.Parameters.AddWithValue("@Tracking_number", Tracking_number.Text.Trim()); cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); } if (Packer6.SelectedValue != "-- ") { cmd.Parameters.AddWithValue("@Order_Number", Order_Number.Text.Trim()); cmd.Parameters.AddWithValue("@Truck_CO", Truck_CO.Text.Trim()); cmd.Parameters.AddWithValue("@ShipMonth", ShipMonth.Text.Trim()); cmd.Parameters.AddWithValue("@ShipDay", ShipDay.Text.Trim()); cmd.Parameters.AddWithValue("@ShipYear", ShipYear.Text.Trim()); cmd.Parameters.AddWithValue("@Packer", Packer6.Text.Trim()); cmd.Parameters.AddWithValue("@Lines", linesPacker6.Text.Trim()); cmd.Parameters.AddWithValue("@Tags", tagsPacker6.Text.Trim()); cmd.Parameters.AddWithValue("@Tracking_number", Tracking_number.Text.Trim()); cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); } messageLabel1.Visible = true; messageLabel2.Visible = true; messageLabel2.Style.Add("color", "Green"); messageLabel1.Text = orderNumber; messageLabel2.Text = " successfully added."; editOrderButton.Visible = false; } cmd.Cancel(); cmd.Dispose(); conn.Close(); } //update Truck FOB if (truck_FOB.Text != "") { checkFOB(Order_Number.Text, Decimal.Parse(truck_FOB.Text)); } clearForm(); }
/* * protected void getBinLocation_Click(object sender, EventArgs e) * { * Boolean s = FindBinLocation(BinLocationItemCode.Text); * if (s == false) * { * alert2.Text = " Item not found"; * alert2.Style.Add("color", "red"); * alert2.Visible = true; * } * else * { * alert2.Visible = false; * } * } */ public String EmployeePriceInfo(String EnteredSku) { try { String sResult = ""; Grid.ID = "Grid"; Grid.Caption = "Employee Price"; Grid.AutoGenerateColumns = false; Grid.AlternatingRowStyle.BackColor = System.Drawing.Color.White; Grid.ForeColor = System.Drawing.Color.Black; Grid.HeaderStyle.BackColor = System.Drawing.Color.Gray; Grid.BorderColor = System.Drawing.Color.Black; Grid.Width = 650; Grid.RowStyle.BackColor = System.Drawing.Color.Gainsboro; Grid.CellPadding = 4; Grid.CellSpacing = 4; Grid.ShowFooter = false; TemplateField tf = null; tf = new TemplateField(); tf.HeaderTemplate = new DynamicGridViewTextTemplate("Sku", DataControlRowType.Header); tf.ItemTemplate = new DynamicGridViewTextTemplate("Sku", DataControlRowType.DataRow); Grid.Columns.Add(tf); tf = new TemplateField(); tf.HeaderTemplate = new DynamicGridViewTextTemplate("Description", DataControlRowType.Header); tf.ItemTemplate = new DynamicGridViewTextTemplate("Description", DataControlRowType.DataRow); Grid.Columns.Add(tf); tf = new TemplateField(); tf.HeaderTemplate = new DynamicGridViewTextTemplate("QAV", DataControlRowType.Header); tf.ItemTemplate = new DynamicGridViewTextTemplate("QAV", DataControlRowType.DataRow); Grid.Columns.Add(tf); tf = new TemplateField(); tf.HeaderTemplate = new DynamicGridViewTextTemplate("Price", DataControlRowType.Header); tf.ItemTemplate = new DynamicGridViewTextTemplate("Price", DataControlRowType.DataRow); Grid.Columns.Add(tf); tf = new TemplateField(); tf.HeaderTemplate = new DynamicGridViewTextTemplate("Min", DataControlRowType.Header); tf.ItemTemplate = new DynamicGridViewTextTemplate("Min", DataControlRowType.DataRow); Grid.Columns.Add(tf); tf = new TemplateField(); tf.HeaderTemplate = new DynamicGridViewTextTemplate("Inner", DataControlRowType.Header); tf.ItemTemplate = new DynamicGridViewTextTemplate("Inner", DataControlRowType.DataRow); Grid.Columns.Add(tf); tf = new TemplateField(); tf.HeaderTemplate = new DynamicGridViewTextTemplate("Case", DataControlRowType.Header); tf.ItemTemplate = new DynamicGridViewTextTemplate("Case", DataControlRowType.DataRow); Grid.Columns.Add(tf); tf = new TemplateField(); tf.HeaderTemplate = new DynamicGridViewTextTemplate("PLine", DataControlRowType.Header); tf.ItemTemplate = new DynamicGridViewTextTemplate("PLine", DataControlRowType.DataRow); Grid.Columns.Add(tf); if (ViewState["employeePriceTable"] != null) { employeePriceTable = (System.Data.DataTable)ViewState["employeePriceTable"]; } using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CN_MAS_RDP"].ConnectionString)) { System.Data.SqlClient.SqlDataReader reader2 = null; System.Data.SqlClient.SqlCommand cmd2 = new System.Data.SqlClient.SqlCommand(); String query = "SELECT ItemNumber, ItemDescription, QtyOnHand, QtyOnSalesOrder, AveCost, QuotedEach, salesUnit, MinSalesUnitInner, MinSalesUnitPurchase, ProductLine FROM vw_Item_Setup where ItemNumber=@Entered_Sku and active ='Y' and ItemNumber like '3%' and (Productline<>'dc' or Productline<>'hold')"; cmd2.CommandText = query; cmd2.CommandType = System.Data.CommandType.Text; cmd2.Connection = conn; cmd2.Parameters.Add("@Entered_Sku", System.Data.SqlDbType.VarChar, 30); cmd2.Parameters["@Entered_Sku"].Value = EnteredSku; conn.Open(); reader2 = cmd2.ExecuteReader(); if (reader2.HasRows == true) { while (reader2.Read()) { employeePriceTable.Rows.Add(); if (!(reader2["ItemNumber"]).Equals(System.DBNull.Value)) { employeePriceTable.Rows[employeePriceTable.Rows.Count - 1]["Sku"] = (String)(reader2["ItemNumber"]); } else { employeePriceTable.Rows[employeePriceTable.Rows.Count - 1]["ItemNumber"] = "Not found"; } if (!(reader2["ItemDescription"]).Equals(System.DBNull.Value)) { employeePriceTable.Rows[employeePriceTable.Rows.Count - 1]["Description"] = (String)(reader2["ItemDescription"]); } else { employeePriceTable.Rows[employeePriceTable.Rows.Count - 1]["Description"] = "Not found"; } if (!(reader2["QtyOnHand"]).Equals(System.DBNull.Value)) { if (!(reader2["QtyOnSalesOrder"]).Equals(System.DBNull.Value)) { employeePriceTable.Rows[employeePriceTable.Rows.Count - 1]["QAV"] = System.Convert.ToString(Math.Round((decimal)(reader2["QtyOnHand"])) - Math.Round((decimal)(reader2["QtyOnSalesOrder"]))); } else { employeePriceTable.Rows[employeePriceTable.Rows.Count - 1]["QAV"] = System.Convert.ToString(Math.Round((decimal)(reader2["QtyOnHand"]))); } } else { employeePriceTable.Rows[employeePriceTable.Rows.Count - 1]["QAV"] = "Not found"; } if (!(reader2["AveCost"]).Equals(System.DBNull.Value)) { if ((decimal)(reader2["AveCost"]) >= 0) { employeePriceTable.Rows[employeePriceTable.Rows.Count - 1]["Price"] = System.Convert.ToString((Math.Round((decimal)(reader2["AveCost"]) * 1000) / 1000) * 1.1m); } else { if (!(reader2["QuotedEach"]).Equals(System.DBNull.Value)) { employeePriceTable.Rows[employeePriceTable.Rows.Count - 1]["Price"] = System.Convert.ToString((Math.Round((decimal)(reader2["QuotedEach"]) * 1000) / 1000) * 1.1m); } else { employeePriceTable.Rows[employeePriceTable.Rows.Count - 1]["Price"] = "Not found"; } } } if (!(reader2["salesUnit"]).Equals(System.DBNull.Value)) { employeePriceTable.Rows[employeePriceTable.Rows.Count - 1]["Min"] = (String)(reader2["salesUnit"]); } else { employeePriceTable.Rows[employeePriceTable.Rows.Count - 1]["Min"] = "Not found"; } if (!(reader2["MinSalesUnitInner"]).Equals(System.DBNull.Value)) { employeePriceTable.Rows[employeePriceTable.Rows.Count - 1]["Inner"] = System.Convert.ToString(Math.Round((decimal)(reader2["MinSalesUnitInner"]))); } else { employeePriceTable.Rows[employeePriceTable.Rows.Count - 1]["Inner"] = "Not found"; } if (!(reader2["MinSalesUnitPurchase"]).Equals(System.DBNull.Value)) { employeePriceTable.Rows[employeePriceTable.Rows.Count - 1]["Case"] = System.Convert.ToString(Math.Round((decimal)(reader2["MinSalesUnitPurchase"]))); } else { employeePriceTable.Rows[employeePriceTable.Rows.Count - 1]["Case"] = "Not found"; } if (!(reader2["ProductLine"]).Equals(System.DBNull.Value)) { employeePriceTable.Rows[employeePriceTable.Rows.Count - 1]["PLine"] = (String)(reader2["ProductLine"]); } else { employeePriceTable.Rows[employeePriceTable.Rows.Count - 1]["PLine"] = "Not found"; } } } else { sResult = "false"; } reader2.Close(); cmd2.Parameters.Clear(); cmd2.Cancel(); cmd2.Dispose(); conn.Close(); Grid.DataSource = employeePriceTable; Grid.DataBind(); GridHolder.Controls.Add(Grid); //An empty result string means we didn't fail. if (sResult == "") { ViewState["employeePriceTable"] = employeePriceTable; return(null); } else { return("Sku not found."); } } } catch (Exception ex) { return(ex.Message); } }
//Get the basic Item Setup information from the database. public Boolean fillItemsInfo(String EnteredSku) { using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CN_MAS_RDP"].ConnectionString)) { //int loopCounter = 0; System.Data.SqlClient.SqlDataReader reader2 = null; System.Data.SqlClient.SqlCommand cmd2 = new System.Data.SqlClient.SqlCommand(); String query = "SELECT * FROM vw_Item_Setup_SKUP where ItemNumber=@Entered_Sku"; cmd2.CommandTimeout = 300; cmd2.CommandText = query; cmd2.CommandType = System.Data.CommandType.Text; cmd2.Connection = conn; cmd2.Parameters.AddWithValue("@Entered_Sku", EnteredSku); conn.Open(); reader2 = cmd2.ExecuteReader(); while (reader2.Read()) { if (reader2["First_DATE_SOLD"] != DBNull.Value) { firstDateSold = Convert.ToDateTime(reader2["First_DATE_SOLD"]); } else { firstDateSold = Convert.ToDateTime("01/01/1900"); } VendorNumberBox.Text = (String)(reader2["VendorNumber"]); DescriptionBox.Text = (String)(reader2["ItemDescription"]); ActiveTextBox.Text = (String)(reader2["Active"]); ReorderTextBox.Text = (String)(reader2["Reorder"]); if (!((reader2["MainCategory"]).Equals(System.DBNull.Value))) { MainCategoryBox.Text = (String)(reader2["MainCategory"]); } AvgCostBox.Text = System.Convert.ToString(Math.Round((decimal)(reader2["AveCost"]) * 1000) / 1000); SB1Box.Text = System.Convert.ToString(Math.Round((decimal)(reader2["SBP1"]) * 1000) / 1000); LeadTimeBox.Text = System.Convert.ToString((int)(decimal)(reader2["LastLeadTime"])); StandardLeadTimeBox.Text = System.Convert.ToString((int)(decimal)(reader2["StandardLeadTime"])); ClassBox.Text = (String)(reader2["Class"]); MOQBox.Text = System.Convert.ToString((int)(decimal)(reader2["MinimumOrderQty"])); adu.Text = System.Convert.ToString(reader2["DAILYUSAGE"]); } reader2.Close(); cmd2.Parameters.Clear(); cmd2.Cancel(); //GET SKUP DATA Int32 iStartYear = System.DateTime.Now.AddYears(-1).Year; Int32 iEndYear = System.DateTime.Now.AddYears(1).Year; Int32 iCol = 0; Int32 iColMax = 0; Double dADU = 0; String sADU = "default"; if (double.TryParse(sADUOverride.Text, out dADU)) { sADU = sADUOverride.Text; } if (bMonthly.Checked) { iColMax = 12; query = "SELECT * FROM dbo.fn_Inventory_SKUP_Monthly_ByItemAndYearRange(@Entered_Sku, @dtStart, @dtEnd, " + sADU + ", default)"; } else { iColMax = 53; query = "SELECT * FROM dbo.fn_Inventory_SKUP_ByItemAndYearRange(@Entered_Sku, @dtStart, @dtEnd, " + sADU + ", default)"; } //Setup the columns needed for the data being returned BoundField bndcCol; for (iCol = grdSKUP.Columns.Count - 1; iCol > 1; iCol--) { grdSKUP.Columns.RemoveAt(iCol); } for (iCol = 1; iCol <= iColMax; iCol++) { bndcCol = new BoundField(); bndcCol.DataField = iCol.ToString(); bndcCol.HeaderText = iCol.ToString(); bndcCol.ItemStyle.CssClass = "skup-DataCell"; if (iColMax == 12) { bndcCol.ShowHeader = true; } grdSKUP.Columns.Add(bndcCol); } cmd2.Parameters.Clear(); cmd2.CommandText = query; cmd2.Parameters.AddWithValue("@Entered_Sku", EnteredSku); cmd2.Parameters.AddWithValue("@dtStart", iStartYear); cmd2.Parameters.AddWithValue("@dtEnd", iEndYear); reader2 = cmd2.ExecuteReader(); grdSKUP.DataSource = reader2; grdSKUP.DataBind(); grdSKUP.Visible = true; Session.Add("dt", reader2); Exportbtn.Visible = true; cmd2.Parameters.Clear(); cmd2.Cancel(); cmd2.Dispose(); conn.Close(); return(true); } }
public static DataSet GetDataSet(string strSQL, List<SqlParameter> Pars) { DataSet dt = new DataSet(); try { Open(); SqlCommand sqlCand = new SqlCommand(strSQL, SqlConn); if ((Pars != null) && (Pars.Count > 0)) { foreach (SqlParameter par in Pars) { sqlCand.Parameters.Add(par); } } SqlDataAdapter sqlda = new SqlDataAdapter(sqlCand); sqlCand.ExecuteNonQuery(); sqlda.Fill(dt); try { for (int i = 0; i < dt.Tables[0].Columns.Count; i++) { dt.Tables[0].Columns[i].Caption = dt.Tables[0].Columns[i].Caption.ToLower().Trim(); } } catch (Exception ex) { //string str4 = strSQL + "\r\n" + ex.Message; //flog_Class.WriteFlog(str4); } sqlCand.Cancel(); } catch (Exception ex) { // flog_Class.WriteFlog(strSQL + "\r\n" + ex.Message); } Close(); return dt; }
protected void Repeater1_ItemDataBound(object sender, RepeaterItemEventArgs e) { if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem) { HiddenField HiddenShopID = e.Item.FindControl("ShopID") as HiddenField; using (SqlConnection conn = new SqlConnection(DBTools.ConnectionString)) { conn.Open(); HtmlTableCell td = e.Item.FindControl("ImgLink") as HtmlTableCell; using (SqlCommand cmd = new SqlCommand("select ShopID,FileName from MenuImg where ShopID=@ShopID", conn)) { #region 設定菜單連結 cmd.Parameters.Add(new SqlParameter("@ShopID", SqlDbType.UniqueIdentifier)); cmd.Parameters[0].Value = Guid.Parse(HiddenShopID.Value); using (SqlDataReader dr = cmd.ExecuteReader()) { LiteralControl startCenter = new LiteralControl(); startCenter.Text = "<center>"; td.Controls.Add(startCenter); while (dr.Read()) { LiteralControl lit = new LiteralControl(); //上線要改 lit.Text = @"<a href=""http://" + Request.Url.Authority + "/Menu/" + dr[1].ToString() + @""" target=""_blank"">" + dr[1].ToString() + "</a>"; //lit.Text = @"<a href=""" + Request.Url.Scheme + "://" + Request.Url.Authority + "/" + Request.Url.Segments[1] + "Menu/" + HttpUtility.UrlEncode(dr[1].ToString()) + @""" target=""_blank"">" + dr[1].ToString() + "</a>"; td.Controls.Add(lit); LiteralControl br = new LiteralControl(); br.Text = "</br>"; td.Controls.Add(br); } LiteralControl endCenter = new LiteralControl(); endCenter.Text = "</center>"; td.Controls.Add(endCenter); cmd.Cancel(); } #endregion /*#region 設定訂單管理按鈕開單者才看得到 HiddenField hidOrderID = e.Item.FindControl("OrderID") as HiddenField; cmd.Parameters.Clear(); cmd.CommandText = "SELECT a.Creator FROM OrderHead a WHERE a.OrderID=@OrderID AND a.Creator=@Creator"; cmd.Parameters.Add("@OrderID", SqlDbType.UniqueIdentifier); cmd.Parameters[0].Value = Guid.Parse(hidOrderID.Value); cmd.Parameters.Add("@Creator", SqlDbType.VarChar, 10); cmd.Parameters[1].Value = Session["userID"]; if (cmd.ExecuteScalar() == null) { Button btnOrderManagement = e.Item.FindControl("OrderManagement") as Button; btnOrderManagement.Visible = false; } #endregion*/ } } } }
public static DataSet GetDataSet(string strSQL, string d_strSQL) { DataSet dt = new DataSet(); try { // flog_Class.WriteFlog(strSQL); Open(); SqlCommand sqlCand = new SqlCommand(strSQL, SqlConn); SqlDataAdapter sqlda = new SqlDataAdapter(sqlCand); sqlCand.ExecuteNonQuery(); sqlda.Fill(dt); try { for (int i = 0; i < dt.Tables[0].Columns.Count; i++) { dt.Tables[0].Columns[i].Caption = dt.Tables[0].Columns[i].Caption.ToLower().Trim(); } } catch (Exception ex) { //string str4 = strSQL + "\r\n" + ex.Message; //flog_Class.WriteFlog(str4); } sqlCand.Cancel(); } catch (Exception ex) { // flog_Class.WriteFlog(strSQL + "\r\n" + ex.Message); } Close(); return dt; }
protected void Page_Load(object sender, EventArgs e) { if (Session["userID"] == null) { Response.Redirect("ADLogin.aspx", true); } if (!IsPostBack) { #region 設定餐點類型 Category.Items.Add(new ListItem("不拘", "不拘")); Category.Items.Add(new ListItem("中餐", "0")); Category.Items.Add(new ListItem("點心", "1")); Category.Items.Add(new ListItem("飲料", "2")); #endregion #region 設定素食選項 IsVegetarianism.Items.Add(new ListItem("不拘", "不拘")); IsVegetarianism.Items.Add(new ListItem("否", "false")); IsVegetarianism.Items.Add(new ListItem("是", "true")); #endregion #region 設定部門別 DepartMent.Items.Add(new ListItem("不拘", "不拘")); using (SqlConnection conn = new SqlConnection(DBTools.ConnectionString)) { conn.Open(); using (SqlCommand cmd = new SqlCommand("SELECT * FROM Department", conn)) { using (SqlDataReader dr = cmd.ExecuteReader()) { //while (dr.Read()) // DepartMent.Items.Add(new ListItem(dr.GetString(1), dr.GetString(1))); DepartMent.DataTextField = "DepartmentName"; DepartMent.DataValueField = "DepartmentName"; DepartMent.DataSource = dr; DepartMent.DataBind(); } cmd.Cancel(); } } #endregion #region 查詢菜單內容 if (!string.IsNullOrWhiteSpace(Request["ShopName"])) { QueryString.Add("ShopName", Request["ShopName"]); ShopName.Text = Request["ShopName"]; } if (!string.IsNullOrWhiteSpace(Request["Category"])) { QueryString.Add("Category", Request["Category"]); Category.Items.FindByValue(Request["Category"]).Selected = true; } if (!string.IsNullOrWhiteSpace(Request["IsVegetarianism"]) && (Request["IsVegetarianism"].Equals("是") || Request["IsVegetarianism"].Equals("否"))) { QueryString.Add("IsVegetarianism", Request["IsVegetarianism"]); IsVegetarianism.Items.FindByText(Request["IsVegetarianism"]).Selected = true; } if (!string.IsNullOrWhiteSpace(Request["Department"])) { QueryString.Add("Department", Request["Department"]); DepartMent.Items.FindByText(Request["Department"]).Selected = true; } if (!string.IsNullOrWhiteSpace(Request["Creator"])) { QueryString.Add("Creator", Request["Creator"]); Creator.Text = Request["Creator"]; } int PageNumber; if (string.IsNullOrWhiteSpace(Request["Page"]) || !int.TryParse(Request["Page"], out PageNumber)) PageNumber = 1; ucPagination.CPage = PageNumber; Query(PageNumber); #endregion } }
protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { //WindowsPrincipal wp = new WindowsPrincipal(WindowsIdentity.GetCurrent()); //string[] cADName = wp.Identity.Name.Split(new Char[] { '\\' }); string cADName = User.Identity.Name.Split(new Char[] { '\\' })[1].Trim().ToUpper(); //如果帳號不是這三個開頭的就轉去註冊頁面 if (!cADName.StartsWith("TA") && !cADName.StartsWith("itap") && !cADName.StartsWith("TOO")) { Response.Redirect("Login.aspx", true); } string userID = null; /*#region 檢查使用者是否在白名單之中 * using (SqlConnection conn = new SqlConnection(DBTools.ConnectionString)) { conn.Open(); using (SqlCommand cmd = new SqlCommand("SELECT userID FROM WhiteList Where userID=@userID", conn)) { cmd.Parameters.Add("@userID", SqlDbType.VarChar, 10); cmd.Parameters[0].Value = cADName; userID = (string)cmd.ExecuteScalar(); } } if (userID == null) { Response.End(); } #endregion*/ #region 檢查該使用者存不存在,存在就進入首頁,不存在就註冊 using (SqlConnection conn = new SqlConnection(DBTools.ConnectionString)) { conn.Open(); using (SqlCommand cmd = new SqlCommand("SELECT UserID FROM UserData WHERE UserID=@UserID", conn)) { cmd.Parameters.Add("@UserID", SqlDbType.VarChar, 10); cmd.Parameters[0].Value = cADName; userID = cmd.ExecuteScalar() as string; cmd.Cancel(); } } if (userID != null) { Session.Add("userID", cADName); Response.Redirect("Index.aspx", true); } else { ViewState.Add("userID", cADName); NickName.Attributes["placeholder"] = "可以輸入任何名稱,中英文都可以,只要別人認得就行"; NickName.Attributes["required"] = ""; NickName.Attributes["autofocus"] = ""; Department.Items.Add(new ListItem("請選擇您的部門")); using (SqlConnection conn = new SqlConnection(DBTools.ConnectionString)) { conn.Open(); using (SqlCommand cmd = new SqlCommand("SELECT DepartmentName,DepartmentID FROM Department", conn)) { using (SqlDataReader dr = cmd.ExecuteReader()) { while (dr.Read()) Department.Items.Add(new ListItem(dr.GetString(0), dr.GetByte(1).ToString())); } cmd.Cancel(); } } } #endregion } }
protected void Update_Click(object sender, EventArgs e) { using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CN_UPS"].ConnectionString)) { System.Data.SqlClient.SqlDataReader reader = null; System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(); //check for duplicates String query = "Delete From Truck where Order_Number =@Order_Number"; cmd.CommandText = query; cmd.CommandType = System.Data.CommandType.Text; cmd.Connection = conn; conn.Open(); cmd.Parameters.AddWithValue("@Order_Number", Order_Number.Text); cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); query = "INSERT INTO Truck VALUES(@Order_Number,@Truck_CO,@ShipMonth,@ShipDay,@ShipYear,@Packer,@Lines,@Tags,@Tracking_number)"; cmd.CommandText = query; cmd.CommandType = System.Data.CommandType.Text; cmd.Connection = conn; if (Packer1.Text != "") { cmd.Parameters.AddWithValue("@Order_Number", Order_Number.Text); cmd.Parameters.AddWithValue("@Truck_CO", Truck_CO.Text); cmd.Parameters.AddWithValue("@ShipMonth", ShipMonth.Text); cmd.Parameters.AddWithValue("@ShipDay", ShipDay.Text); cmd.Parameters.AddWithValue("@ShipYear", ShipYear.Text); cmd.Parameters.AddWithValue("@Packer", Packer1.Text); cmd.Parameters.AddWithValue("@Lines", linesPacker1.Text); cmd.Parameters.AddWithValue("@Tags", tagsPacker1.Text); cmd.Parameters.AddWithValue("@Tracking_number", Tracking_number.Text); cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); } if (Packer2.Text != "") { cmd.Parameters.AddWithValue("@Order_Number", Order_Number.Text); cmd.Parameters.AddWithValue("@Truck_CO", Truck_CO.Text); cmd.Parameters.AddWithValue("@ShipMonth", ShipMonth.Text); cmd.Parameters.AddWithValue("@ShipDay", ShipDay.Text); cmd.Parameters.AddWithValue("@ShipYear", ShipYear.Text); cmd.Parameters.AddWithValue("@Packer", Packer2.Text); cmd.Parameters.AddWithValue("@Lines", linesPacker2.Text); cmd.Parameters.AddWithValue("@Tags", tagsPacker2.Text); cmd.Parameters.AddWithValue("@Tracking_number", Tracking_number.Text); cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); } if (Packer3.Text != "") { cmd.Parameters.AddWithValue("@Order_Number", Order_Number.Text); cmd.Parameters.AddWithValue("@Truck_CO", Truck_CO.Text); cmd.Parameters.AddWithValue("@ShipMonth", ShipMonth.Text); cmd.Parameters.AddWithValue("@ShipDay", ShipDay.Text); cmd.Parameters.AddWithValue("@ShipYear", ShipYear.Text); cmd.Parameters.AddWithValue("@Packer", Packer3.Text); cmd.Parameters.AddWithValue("@Lines", linesPacker3.Text); cmd.Parameters.AddWithValue("@Tags", tagsPacker3.Text); cmd.Parameters.AddWithValue("@Tracking_number", Tracking_number.Text); cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); } if (Packer4.Text != "") { cmd.Parameters.AddWithValue("@Order_Number", Order_Number.Text); cmd.Parameters.AddWithValue("@Truck_CO", Truck_CO.Text); cmd.Parameters.AddWithValue("@ShipMonth", ShipMonth.Text); cmd.Parameters.AddWithValue("@ShipDay", ShipDay.Text); cmd.Parameters.AddWithValue("@ShipYear", ShipYear.Text); cmd.Parameters.AddWithValue("@Packer", Packer4.Text); cmd.Parameters.AddWithValue("@Lines", linesPacker4.Text); cmd.Parameters.AddWithValue("@Tags", tagsPacker4.Text); cmd.Parameters.AddWithValue("@Tracking_number", Tracking_number.Text); cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); } if (Packer5.Text != "") { cmd.Parameters.AddWithValue("@Order_Number", Order_Number.Text); cmd.Parameters.AddWithValue("@Truck_CO", Truck_CO.Text); cmd.Parameters.AddWithValue("@ShipMonth", ShipMonth.Text); cmd.Parameters.AddWithValue("@ShipDay", ShipDay.Text); cmd.Parameters.AddWithValue("@ShipYear", ShipYear.Text); cmd.Parameters.AddWithValue("@Packer", Packer5.Text); cmd.Parameters.AddWithValue("@Lines", linesPacker5.Text); cmd.Parameters.AddWithValue("@Tags", tagsPacker5.Text); cmd.Parameters.AddWithValue("@Tracking_number", Tracking_number.Text); cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); } if (Packer6.Text != "") { cmd.Parameters.AddWithValue("@Order_Number", Order_Number.Text); cmd.Parameters.AddWithValue("@Truck_CO", Truck_CO.Text); cmd.Parameters.AddWithValue("@ShipMonth", ShipMonth.Text); cmd.Parameters.AddWithValue("@ShipDay", ShipDay.Text); cmd.Parameters.AddWithValue("@ShipYear", ShipYear.Text); cmd.Parameters.AddWithValue("@Packer", Packer6.Text); cmd.Parameters.AddWithValue("@Lines", linesPacker6.Text); cmd.Parameters.AddWithValue("@Tags", tagsPacker6.Text); cmd.Parameters.AddWithValue("@Tracking_number", Tracking_number.Text); cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); } messageLabel1.Visible = true; messageLabel2.Visible = true; messageLabel2.Style.Add("color", "Green"); messageLabel1.Text = orderNumber; messageLabel2.Text = " successfully updated."; editOrderButton.Visible = false; Order_Number.Enabled = true; Update.Visible = false; Enter.Visible = true; cmd.Cancel(); cmd.Dispose(); conn.Close(); } //update Truck FOB if (truck_FOB.Text != null || truck_FOB.Text != "") { checkFOB(Order_Number.Text, Decimal.Parse(truck_FOB.Text)); } clearForm(); }