新鸟文章:foreach里的Sqlcommand(有SqlTransaction)

最近弄个读取Excel文件并写进数据库的东东。(很简单的东西,网上搜搜一大萝)

准备一个fileupload控件。两个Button(一个叫BTShow,另一个叫BTUpload),一个GridView

fileupload控件没做什么东西,就是通过点击“游览...”按钮(内置的),

(注:这个Text不可以修改,不过样式还是可以的。有一些人想改成"Browse...",其实这个是根据系统的设置语言走的,不过也有一些人利用TextBox和Button来达到这个目的,也就是隐藏fileupload控件,然后调用其相关事件。具体做法,各位自己网上找啦。)

来将文件的绝对路径拿出来放在fileupload的显示框里。其实也可以直接在显示框里输入,但容易出错。所以我们可以禁止这个。

看下面代码:

<asp:FileUpload ID="FUSalBud" runat="server" Height="30px" Width="224px" onkeydown="event.returnValue=false;" onpaste="return false;" /> 

选择文件后:

在BTShow点击事件里写:

 1    protected void BTShow_Click(object sender, EventArgs e)
  2    {
  3        if (FUSalBud.HasFile)
  4        {
  5            //读取文件
  6            //获取文件的后缀名
  7            string FileExtension = Path.GetExtension(FUSalBud.FileName).ToLower();
  8            //获取文件的名称(不包括后缀后)
  9            string FileName = Path.GetFileNameWithoutExtension(FUSalBud.FileName);
10            //用来判断所允许的文件类型
11            string[] AllowExtension = { ".txt", ".gif", ".asp", ".aspx", ".doc", ".xls" };
12            string FolderName = "";
13            string FileNameExtension = "";
14            string FilePath = "";
15            bool flag = false;
16            for (int i = 0; i < AllowExtension.Length; i++)
17            {
18                if (FileExtension == AllowExtension[i])
19                {
20                    try
21                    {
22                        //如果是Excel文件刚放到一个独立文件夹里
23                        if (FileExtension == ".xls")
24                        {
25                            FolderName = "~/UploadFiles/ExcelFiles/";
26                        }

27                        else
28                            FolderName = "~/UploadFiles/";
29
30                        //给上传到服务器的文件起名字
31                        FileNameExtension = FileName + System.DateTime.Now.Second + FileExtension;
32                        //设置文件所要放的地放(绝对路径)
33                        FilePath = Server.MapPath(FolderName) + FileNameExtension;
34                        //ViewState["FilePathName"] = FilePath;
35                        /*
36                         Response.Write(FilePath + "<br>");
37                         Response.Write(FUSalBud.FileName + " 文件<br>");
38                         Response.Write(FileExtension + "<br>");
39                        */

40                        //这里就是执行FileUpload的SaveAs方法保存上传的文件。
41                        FUSalBud.SaveAs(FilePath);
42                        Response.Write("<br>上传成功!<br>类型:" + FUSalBud.PostedFile.ContentType + "  大小:" + Math.Round((FUSalBud.PostedFile.ContentLength / 1024.0), 2) + " KB");
43                        flag = true;
44                        //将这个上传到数据库的按钮设为可用。
45                        BTUpload.Enabled = true;
46                    }

47                    catch (Exception ex)
48                    {
49                        Response.Write("<br>上传失败!" + ex.Message + "<br>");
50                    }

51                }

52            }

53            if (!flag)
54            {
55                Response.Write("<br>文件格式不正确<BR/>");
56            }

57
58            //读取文件至GridView
59            /*
60                    string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" +
61                        "Extended Properties=Excel 8.0;" +
62                        "data source=" + xlsPath;
63             *
64                * 数据提供程序使用Jet,同时需要指定Extended Properties 关键字设置 Excel 特定的属性,不同版本的Excel对应不同的属性值:用于 Extended Properties 值的有效 Excel 版本。
65                    对于 Microsoft Excel 8.0 (97)、9.0 (2000) 和 10.0 (2002) 工作簿,请使用 Excel 8.0。
66                    对于 Microsoft Excel 5.0 和 7.0 (95) 工作簿,请使用 Excel 5.0。
67                    对于 Microsoft Excel 4.0 工作簿,请使用 Excel 4.0。
68                    对于 Microsoft Excel 3.0 工作簿,请使用 Excel 3.0。
69             * HDR=Yes,表示Excel文件里的sheet中的第一行数据表示的是字段
70             * IMEX=1,表示采用数据混合的类型。
71             * Extended Properties=\\"Excel 8.0;HDR=Yes;IMEX=1\\";这里面用到了转义字符,因为要加双引号("")。否则报错。
72             */

73            string connExcel = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Extended Properties=\\"Excel 8.0;HDR=Yes;IMEX=1\\";" + "data source=" + FilePath;
74            if (FileNameExtension != "" && FileExtension == ".xls")
75            {
76                //读取Excel的sheet数据。    [Sheet1$]中的Sheet1为Excel中Sheet里的名称。其格式就是写成这样。
77                string SQL = "select * from [Sheet1$]";
78                //以下跟利用SqlClient读取数据库的DATA一样了。不多说了。
79                OleDbConnection conn = new OleDbConnection(connExcel);
80                try
81                {
82                    conn.Open();
83                    OleDbDataAdapter oda = new OleDbDataAdapter(SQL, conn);
84                    DataSet ds = new DataSet();
85                    oda.Fill(ds, "Sheet");
86                    GridView1.DataSource = ds;
87                    GridView1.DataBind();
88                }

89                catch (Exception ex)
90                {
91                    Response.Write("<br>读取Excel文件失败: " + ex.Message);
92                }

93                finally
94                {
95                    conn.Close();
96                    File.Delete(FilePath);
97                }

98            }

99        }

100        else
101        {
102            Response.Write("请选择文件!");
103        }

104    }

 

读取后,现在就将GridView里的数据写进数据库。在BTUpload的单击事件里写:

1   protected void BTUpload_Click(object sender, EventArgs e)
2    {
3        SqlConnection conn = sharefunctions.GetConnection();
4        StringBuilder sSQL = new StringBuilder("INSERT INTO SisSalesBudget VALUES");
5        sSQL.Append(" ('10',@CmpCode,@YEAR,@Period,@Version,@SalesOrg,@DistChnl,@Division,@SalesOff,@SalesGrp,@BudgetGroup,@CustomerNo,@MtrlNo, ");
6        sSQL.Append(" @Qty,@HL,@ActiveVer,'CFM',@RecUser,CONVERT(VARCHAR(8),GETDATE(),112),CONVERT(VARCHAR(5),GETDATE(),108),@LstUser,GETDATE()) ");
7        string InsertSQL = sSQL.ToString();
8      
9        try
10        {
11            conn.Open();
12        }

13        catch (Exception ex)
14        {
15            LabError.Text = "Connection SQLDB Error!" + ex.Message + "<BR/>";
16        }

17        SqlTransaction trans = conn.BeginTransaction();
18        DataSet ds = null;
19        string sMtrlNo = ",";
20       // SqlCommand comd = new SqlCommand(InsertSQL, conn);
21        bool Succeed = true;
22        try
23        {
24           // comd.Transaction = trans;
25            foreach (GridViewRow gr in GridView1.Rows)
26            {
27                //我在这里加上SqlTransaction
28                //注意:我并没有显式释放掉SqlCommand,其实也不知该怎么做好,望高手看点。不过问题暂时看不出。
29                SqlCommand comd = new SqlCommand(InsertSQL, conn,trans);
30                sMtrlNo = gr.Cells[11].Text;
31                ds = SalBudDs.DsMtrlUomChange("10", sMtrlNo, "HL");
32                if (ds.Tables["TabMtrlUom"].Rows.Count > 0)
33                {
34                    //Debug
35                    //Response.Write(gr.Cells[9].Text + "<br>");
36
37                    double BaseQty = Convert.ToDouble(ds.Tables["TabMtrlUom"].Rows[0]["BaseQty"].ToString());
38                    double AltQty = Convert.ToDouble(ds.Tables["TabMtrlUom"].Rows[0]["AltQty"].ToString());
39                    double ActQty = Convert.ToDouble(gr.Cells[12].Text);
40                    double HL = ActQty * (AltQty / BaseQty);
41                    comd.Parameters.Add("@CmpCode", SqlDbType.VarChar, 4).Value = gr.Cells[0].Text;
42                    comd.Parameters.Add("@YEAR", SqlDbType.VarChar, 4).Value = gr.Cells[1].Text;
43                    comd.Parameters.Add("@Period", SqlDbType.VarChar, 4).Value = gr.Cells[2].Text;
44                    comd.Parameters.Add("@Version", SqlDbType.Int, 4).Value = Convert.ToInt16(gr.Cells[3].Text);
45                    comd.Parameters.Add("@SalesOrg", SqlDbType.VarChar, 4).Value = gr.Cells[4].Text;
46                    comd.Parameters.Add("@DistChnl", SqlDbType.VarChar, 2).Value = gr.Cells[5].Text;
47                    comd.Parameters.Add("@Division", SqlDbType.VarChar, 2).Value = gr.Cells[6].Text;
48                    comd.Parameters.Add("@SalesOff", SqlDbType.VarChar, 4).Value = gr.Cells[7].Text;
49                    comd.Parameters.Add("@BudgetGroup", SqlDbType.VarChar, 2).Value = gr.Cells[8].Text;
50                    comd.Parameters.Add("@SalesGrp", SqlDbType.VarChar, 3).Value = gr.Cells[9].Text;
51                    comd.Parameters.Add("@CustomerNo", SqlDbType.VarChar, 10).Value = gr.Cells[10].Text;
52                    comd.Parameters.Add("@MtrlNo", SqlDbType.VarChar, 18).Value = sMtrlNo;
53                    comd.Parameters.Add("@Qty", SqlDbType.Float).Value = ActQty;
54                    comd.Parameters.Add("@HL", SqlDbType.Float).Value = HL;
55                    comd.Parameters.Add("@ActiveVer", SqlDbType.VarChar, 5).Value = gr.Cells[13].Text;
56                    comd.Parameters.Add("@RecUser", SqlDbType.VarChar, 20).Value = Session["PmsUserID"].ToString();
57                    comd.Parameters.Add("@LstUser", SqlDbType.VarChar, 20).Value = Session["PmsUserID"].ToString();
58                    int m = comd.ExecuteNonQuery();
59                    if (m == 0)
60                    {
61                        Succeed = false;
62                    }

63                }

64                else
65                {
66                    Succeed = false;
67                    break;
68                }

69            }

70
71            if (Succeed)
72            {
73                trans.Commit();
74                BTUpload.Enabled = false;
75                LabError.Text = "Insert Data succeed!";
76            }

77            else
78            {
79                Response.Write("The MtrlNo: " + sMtrlNo + " is invalid.<br/>");
80                trans.Rollback();
81            }

82        }

83        catch (Exception ex)
84        {
85            //catch到的错所要回滚的。无论开多少个begintrans,只要一个rollback 就搞定,回到最初状态。但commit trans不是,开多少个,就要commit多少次。
86            //另,commit N次后,如果最后一个的是rollback ,也只会回到最初状态。
87            trans.Rollback();
88            LabError.Text = "Insert Data Error!" + ex.Message + "<BR/>";
89        }

90        finally
91        {
92            //这里就将SqlTransaction释放掉了。只要不出错,Dispose()都不会call Rollback()。
93            trans.Dispose();
94            conn.Close();
95        }

96    }

搞定。

第一次写,肯定会有错的,望见凉。有时间就来。哈哈。。。我要成为高手啊。

posted @ 2009-03-30 23:03  BePast  阅读(875)  评论(0编辑  收藏  举报