Excel中數據導入到SQL Server數據庫

2019-11-03 08:34:41

字體：大中小

來源：轉載

供稿：網友

using System.Data.SqlClient;

using System.Data.OleDb;

  PRivate void tsmiImportTeacherInfo_Click(object sender, EventArgs e)
        {
            DataSet ds;
            if (ofdImport.ShowDialog()==DialogResult.OK)
            {
                ds = ImportExcel(this.ofdImport.FileName);  //獲得Excel
            }
            else
            {
                return;
            }
            int odr = 0;
            SqlConnection cn = db.sqlconnection();
            try
            {
                cn.Open();
                string str = "insert into Teacher (TeacherId,Pwd,eachCourse,TeacherName) values(@TeacherId,@Pwd,@TeachCourse,@TeacherName)";
                SqlCommand cmd = db.sqlcommand(str,cn);

                int dsLength = ds.Tables[0].Rows.Count;   //獲得Excel中數據長度

                for (int i = 0; i < dsLength; i++)
                {
                    cmd.Parameters.Add("@TeacherId", SqlDbType.VarChar, 50).Value = ds.Tables["Teacher "].Rows[i][0];
                    cmd.Parameters.Add("@Pwd", SqlDbType.VarChar, 50).Value = ds.Tables["Teacher "].Rows[i][1];
                    cmd.Parameters.Add("@TeachCourse", SqlDbType.VarChar, 300).Value = ds.Tables["Teacher "].Rows[i][2];
                    cmd.Parameters.Add("@TeacherName", SqlDbType.VarChar, 50).Value = ds.Tables["Teacher "].Rows[i][3];

                    odr = cmd.ExecuteNonQuery();//提交
                    cmd.Parameters.Clear();    //清除前的賦值
                }

                //如果查到了數據，才使控制分頁按鈕生效
                if (odr > 0)
                {
                    MessageBox.Show("導入成功！", "系統提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                cn.Close();
            }
            catch (Exception ex)
            {

                throw ex;
            }

        }

        public static DataSet ImportExcel(string file)
        {
            FileInfo FileInfo = new FileInfo(file);
            if (!FileInfo.Exists)
            {
                return null;
            }

            string strcn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + file + ";Extended Properties='Excel 12.0 xml;HDR=YES;IMEX=1;'";

            OleDbConnection cn = new OleDbConnection(strcn);
            DataSet ds = new DataSet();
            try
            {
                cn.Open();
                string strcmd = "select * from [Sheet1$]";
                OleDbDataAdapter da = new OleDbDataAdapter(strcmd,cn);
                da.Fill(ds, "Teacher ");
                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

本文來自CSDN博客，轉載請標明出處：http://blog.csdn.net/crx05/archive/2009/12/17/5023589.aspx

上一篇：Mysql中limit的用法詳解

下一篇：探密微軟SQL Server 2008中新壓縮特性