Esempio n. 1
0
 /// <summary>
 /// 执行多条SQL语句,实现数据库事务。
 /// </summary>
 /// <param name="SQLStringList">多条SQL语句</param>
 public int ExecuteSqlTran(List <String> SQLStringList)
 {
     using (SqlConnection conn = new SqlConnection(GetDBPwd.GetDBConString_MSSQL(ConfigurationManager.AppSettings["AuthCode"].ToString(),
                                                                                 ConfigurationManager.AppSettings["UserName"].ToString(),
                                                                                 ConfigurationManager.AppSettings["DB"].ToString(),
                                                                                 ConfigurationManager.AppSettings["Env"].ToString())))
     {
         conn.Open();
         SqlCommand cmd = new SqlCommand();
         cmd.Connection = conn;
         SqlTransaction tx = conn.BeginTransaction();
         cmd.Transaction = tx;
         try
         {
             int count = 0;
             for (int n = 0; n < SQLStringList.Count; n++)
             {
                 string strsql = SQLStringList[n];
                 if (strsql.Trim().Length > 1)
                 {
                     cmd.CommandText = strsql;
                     int current = cmd.ExecuteNonQuery();
                     count += current == -1 ? 0 : current;
                 }
             }
             tx.Commit();
             return(count);
         }
         catch (Exception)
         {
             tx.Rollback();
             return(0);
         }
     }
 }
Esempio n. 2
0
    //exec SP_Y_Inventory 'PD1111-2011-12-02-0001','aaaaaa',0
    /// <summary>
    /// 执行多条SQL语句,实现数据库事务。
    /// </summary>
    /// <param name="SQLStringList">多条SQL语句</param>
    public string ExecuteProcdure(List <String> SQLStringList, out int count)
    {
        string result = "";

        count = 0;
        using (SqlConnection conn = new SqlConnection(GetDBPwd.GetDBConString_MSSQL(ConfigurationManager.AppSettings["AuthCode"].ToString(),
                                                                                    ConfigurationManager.AppSettings["UserName"].ToString(),
                                                                                    ConfigurationManager.AppSettings["DB"].ToString(),
                                                                                    ConfigurationManager.AppSettings["Env"].ToString())))
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            try
            {
                for (int n = 0; n < SQLStringList.Count; n++)
                {
                    string strsql = SQLStringList[n];
                    if (strsql.Trim().Length > 1)
                    {
                        cmd.CommandText = strsql;
                        string Temp = cmd.ExecuteScalar().ToString();
                        if (Temp != "0")
                        {
                            result += Temp + " 条码错误;<br />";
                        }
                        else
                        {
                            count++;
                        }
                    }
                }
                return(result);
            }
            catch (Exception)
            {
                return("");
            }
            finally
            {
                conn.Close();
            }
        }
    }
Esempio n. 3
0
    protected void Report_DataBind(object sender, StoreRefreshDataEventArgs e)
    {
        if (Request["shopid"] == null)
        {
            return;
        }
        CJ_DevelopHelper.SqlConn_Str = GetDBPwd.GetDBConString_MSSQL(ConfigurationManager.AppSettings["AuthCode"].ToString(),
                                                                     ConfigurationManager.AppSettings["UserName"].ToString(),
                                                                     ConfigurationManager.AppSettings["DB"].ToString(),
                                                                     ConfigurationManager.AppSettings["Env"].ToString());
        CJ_DevelopHelper.SqlStr = "select depotid from j_user where userid='" + Request["shopid"].ToString() + "'";
        DataTable dt = CJ_DevelopHelper.SQL_ReturnDateTable;

        if (dt.Rows.Count <= 0)
        {
            return;
        }
        CJ_DevelopHelper.SqlStr     = "select xl 系列,stycolorid 款式,get_date 上货日期,nums 销售,k_num 库存 from T_skctopsale_message where depotid1='" + dt.Rows[0][0].ToString() + "' and nums=0 order by xl desc";
        Report_MainStore.DataSource = CJ_DevelopHelper.SQL_ReturnDateTable;
        Report_MainStore.DataBind();
    }
Esempio n. 4
0
    protected void Btnsave_Click(object sender, DirectEventArgs e)
    {
        try
        {
            string filename = UpFile.PostedFile.FileName;
            string ext      = Path.GetExtension(filename).ToLower();
            if (ext != ".xls" && ext != ".xlsx")
            {
                X.Msg.Notify("失败", "<font color='red'>文件格式不正确,仅支持.xls(Excel)格式文件!</font>").Show();
                return;
            }
            filename = DateTime.Now.ToString("yyyyMMddHHmmss") + ext;
            UpFile.PostedFile.SaveAs(Server.MapPath("File/" + filename));
            DataTable dt = new DataTable();

            CJ_DevelopHelper.OtherCon_Str = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("File/" + filename) + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1;'";
            CJ_DevelopHelper.SqlStr       = "select 款式编号 as sku,色号 as color,F,F4,F5,F6,F7,F8,F9 from [sheet1$] where 款式编号<>''";
            dt = CJ_DevelopHelper.Other_ReturnDateTable;

            if (dt.Rows.Count <= 0)
            {
                X.Msg.Alert("提醒", "导入的文件无记录!").Show();
                return;
            }
            string errorSKU = "";
            CJ_DevelopHelper.SqlConn_Str = GetDBPwd.GetDBConString_MSSQL(ConfigurationManager.AppSettings["AuthCode"].ToString(),
                                                                         ConfigurationManager.AppSettings["UserName"].ToString(),
                                                                         ConfigurationManager.AppSettings["DB"].ToString(),
                                                                         ConfigurationManager.AppSettings["Env"].ToString());
            string        allsku   = "";
            int           rowcount = 0;
            StringBuilder errtxt   = new StringBuilder();
            errtxt.AppendLine("款式编号\t色号\t尺码\t错因");
            foreach (DataRow dr in dt.Rows)
            {
                string sku = dr[0].ToString().ToUpper();
                if (sku.Length <= 0)
                {
                    continue;
                }
                for (int i = 2; i <= 8; i++)
                {
                    string size = i == 2 ? "F" : (i - 1).ToString();

                    if (dr[i].ToString().Trim().Length > 0)
                    {
                        if (int.Parse(dr[i].ToString()) < 0)
                        {
                            errorSKU += sku + dr[1].ToString().Trim() + size + " 数量为负;<br />";
                            errtxt.AppendLine(sku + "\t" + dr[1].ToString().Trim() + "\t" + size + "\t数量为负");
                        }
                        else
                        {
                            allsku += "'" + sku + dr[1].ToString().Trim() + size + "',";
                            rowcount++;
                        }
                    }
                }
            }
            CJ_DevelopHelper.SqlStr = "select clothingid,styleid,colorid from j_clothing where clothingid in (" + allsku.TrimEnd(',') + ")";
            DataTable dtDB = CJ_DevelopHelper.SQL_ReturnDateTable;
            //找到错款编号
            if (rowcount != dtDB.Rows.Count)
            {
                string [] sku = allsku.TrimEnd(',').Split(',');
                for (int i = 0; i < sku.Length; i++)
                {
                    int isfind = 0;
                    foreach (DataRow dr in dt.Rows)
                    {
                        if (dr[0].ToString().ToUpper() == sku[i].ToUpper().Trim('\''))
                        {
                            isfind = 1; break;
                        }
                    }
                    if (isfind == 0)
                    {
                        errorSKU += sku[i].Trim('\'') + " 款式错误;<br />";
                        errtxt.AppendLine(sku[i].Trim('\'').Substring(0, sku[i].Trim('\'').Length - 2)
                                          + "\t" + sku[i].Trim('\'').Substring(sku[i].Trim('\'').Length - 2, 1)
                                          + "\t" + sku[i].Trim('\'').Substring(sku[i].Trim('\'').Length - 1, 1)
                                          + "\t该款式无此尺码");
                    }
                }
            }
            if (errorSKU != "")
            {
                string str = DateTime.Now.ToString("yyyyMMddHHmmss") + "Back_ERROR" + ".txt";
                CJ_DevelopHelper.FilePath    = Server.MapPath("File/") + str;
                CJ_DevelopHelper.FileContent = errtxt.ToString();
                if (!CJ_DevelopHelper.WriteFile)
                {
                    X.Msg.Alert("提醒", "数据部分存在错误!<br />但生成异常文件时错误,请重试!").Show();
                    return;
                }
                X.Msg.Alert("提醒", "存在错误记录,请先检查!<br />请下载异常款文件<a href='File/" + str + "' target='_blank'>" + str + "</a>,修改后重新提交<br />错误提示:<br />" + errorSKU).Show();
                return;
            }
            checknum_MainStore.DataSource = dt;
            checknum_MainStore.DataBind();
            if (File.Exists(Server.MapPath("File/" + filename)))
            {
                File.Delete(Server.MapPath("File/" + filename));
            }
        }
        catch (Exception error)
        {
            X.Msg.Alert("错误", error.Message + "!请认真检查文档格式<br />各个款式退货数量是否存在非数字").Show();
        }
    }
Esempio n. 5
0
 private void LongAction(object state)
 {
     try
     {
         DataTable dt       = (DataTable)Session["mainDT"];
         string    errorSKU = "";
         CJ_DevelopHelper.SqlConn_Str = GetDBPwd.GetDBConString_MSSQL(ConfigurationManager.AppSettings["AuthCode"].ToString(),
                                                                      ConfigurationManager.AppSettings["UserName"].ToString(),
                                                                      ConfigurationManager.AppSettings["DB"].ToString(),
                                                                      ConfigurationManager.AppSettings["Env"].ToString());
         int           totalnum = 0;
         StringBuilder errtxt   = new StringBuilder();
         errtxt.AppendLine("条码\t数量");
         int           i      = 1;//记录
         List <string> sqlist = new List <string>();
         this.Session["Total"] = dt.Rows.Count;
         int successDataCount = 0;
         foreach (DataRow dr in dt.Rows)
         {
             string sku = dr[0].ToString().ToUpper();
             if (sku.Length <= 0)
             {
                 continue;
             }
             if (int.Parse(dr[1].ToString()) <= 0)
             {
                 errorSKU += sku + " 盘点数量为负或零;<br />";
                 continue;
             }
             else
             {
                 sqlist.Add("exec SP_Y_Inventory '" + Session["listno"].ToString() + "','" + sku + "'," + dr[1].ToString().Trim() + "");
                 if (i % 50 == 0)//每50条,提交到数据库执行
                 {
                     int    tmpcount = 0;
                     string temp     = ExecuteProcdure(sqlist, out tmpcount);
                     if (temp != "")
                     {
                         errorSKU += temp;
                     }
                     sqlist.Clear();
                     successDataCount += tmpcount;
                     this.Session["LongActionProgress"] = i;
                 }
             }
             i++;
             totalnum += int.Parse(dr[1].ToString());
         }
         if (sqlist.Count > 0)
         {
             int    tmpcount = 0;
             string temp     = ExecuteProcdure(sqlist, out tmpcount);
             if (temp != "")
             {
                 errorSKU += temp;
             }
             sqlist.Clear();
             successDataCount += tmpcount;
             this.Session["LongActionProgress"] = i;
         }
         sqlist.Add("exec F22_RefreshDj 'd_inventory','" + Session["listno"].ToString() + "' ;");
         ExecuteSqlTran(sqlist);
         if (errorSKU != "")
         {
             this.Session["result"] = "导入成功" + successDataCount.ToString() + "条!但部分数据导入失败,存在错误,错误数据请检查后重新提交<br />错误数据:<br />" + errorSKU;
         }
         else
         {
             this.Session["result"] = "恭喜您,全部导入成功(" + successDataCount.ToString() + "条)!";
         }
     }
     catch (Exception error)
     {
         this.Session["result"] = "异常:" + error.Message + "!请认真检查盘点文档格式";
     }
     finally
     {
         this.Session.Remove("mainDT");
         this.Session.Remove("LongActionProgress");
         this.Session.Remove("listno");
     }
 }