createtbform.cs

用Excel建表

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Collections;
using System.Data.OleDb;

namespace DBApplicateSystem
{
    public partial class CreateTbForm : Form
    {
        OpenFileDialog openexceldialog = new OpenFileDialog();
        DataSet ds = new DataSet();
        string sqlstr = string.Empty;
        string constr = string.Empty;
        public CreateTbForm()
        {
            InitializeComponent();
        }

        private void btnConfigSQLServer2005_Click(object sender, EventArgs e)
        {
            // 需.Net库adodb.dll和COM库(Microsoft Ole DB Service Component 1.0 Type Library)支持
            try
            {
                ADODB._Connection ADOcon;
                ADOcon = new ADODB.ConnectionClass();
                MSDASC.DataLinks msLink = new MSDASC.DataLinks();
                ADOcon = (ADODB._Connection)msLink.PromptNew();
                if (!object.Equals(null, ADOcon))
                {
                    lbconnectionstr.Text = lbconnectionstr.Text +ADOcon.ConnectionString.Substring(ADOcon.ConnectionString.IndexOf(';') + 1);
                    constr = ADOcon.ConnectionString.Substring(ADOcon.ConnectionString.IndexOf(';') + 1);
                }

            }
            catch (Exception ee)
            {
                MessageBox.Show(ee.ToString());
            }

        }

        private void browseBt_Click(object sender, EventArgs e)
        {
            if (openexceldialog.ShowDialog() == DialogResult.OK)
            {
                ExcelPathTextBox.Text = openexceldialog.FileName;
                progressBar1.Style = ProgressBarStyle.Marquee;
                progressBar1.Visible = true;

            }
            else
            {
                return;
            }
            bgWbrowse.RunWorkerAsync();

        }

        private void bgWbrowse_DoWork(object sender, DoWorkEventArgs e)
        {
            try
            {
                string filename = ExcelPathTextBox.Text;
                string conn = " Provider = Microsoft.Jet.OLEDB.4.0;Data Source =" + filename + ";Extended Properties=Excel 8.0";
                OleDbConnection objcon = new OleDbConnection(conn);
                string strsql = "select * from [数据字典$]";
                OleDbDataAdapter adpt = new OleDbDataAdapter(strsql, objcon);
                adpt.Fill(ds);
            }
            catch (Exception ee)
            {
                MessageBox.Show(ee.ToString());
            }
        }

        private void bgWbrowse_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            dGVPriview.DataSource = ds.Tables[0];
            progressBar1.Visible = false;
        }

        private void btCreate_Click(object sender, EventArgs e)
        {
            try
            {
                createTb();
            }
            catch (Exception ee)
            {
                MessageBox.Show(ee.ToString());
                return;
            }
           MessageBox.Show("建表成功！");
           
        }
        private void createTb()
        {
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                string tablename = ds.Tables[0].Rows[i][0].ToString();
                if (tablename != "")
                {
                    string colname = ds.Tables[0].Rows[i]["字段名"].ToString();
                    string type = ds.Tables[0].Rows[i]["类型"].ToString().ToLower();
                    string length = "(" + ds.Tables[0].Rows[i]["长度"].ToString() + ")";
                    string isnull = ds.Tables[0].Rows[i]["允许空"].ToString();
                    string isprimarykey = ds.Tables[0].Rows[i]["主键"].ToString();
                    sqlstr = "create table " + tablename + " ( ";
                    sqlstr = sqlstr + colname + " ";
                    sqlstr = sqlstr + type;
                    if (type != "int" && type != "datetime" && type != "float" && type != "money" && type != "tinyint" && type != "bigint" && type != "ntext" && type != "bit")
                    sqlstr = sqlstr + length;
                    if (isnull == "否")
                        sqlstr = sqlstr + " NOT NULL ";
                    if (isprimarykey == "是")
                        sqlstr = sqlstr + " PRIMARY KEY ";
                    sqlstr = sqlstr + ",";

                    for (int j = 1; i + j < ds.Tables[0].Rows.Count; j++)
                    {
                        tablename = ds.Tables[0].Rows[i + j][0].ToString();
                        if (tablename != "" )
                        {
                            sqlstr = sqlstr.Substring(0, sqlstr.Length - 1);
                            sqlstr = sqlstr + ")";
                            SqlConnection objcon = new SqlConnection(constr);
                            objcon.Open();
                            SqlCommand objcom = new SqlCommand(sqlstr, objcon);
                            objcom.CommandTimeout = 60000;
                            objcom.ExecuteNonQuery();
                            objcon.Close();
                            break;
                        }
                        else
                        {
                            colname = ds.Tables[0].Rows[i + j]["字段名"].ToString();
                            type = ds.Tables[0].Rows[i + j]["类型"].ToString().ToLower();
                            length = "(" + ds.Tables[0].Rows[i + j]["长度"].ToString() + ")";
                            isnull = ds.Tables[0].Rows[i + j]["允许空"].ToString();
                            isprimarykey = ds.Tables[0].Rows[i + j]["主键"].ToString();
                            sqlstr = sqlstr + colname + " ";
                            sqlstr = sqlstr + type;
                            if (type != "int" && type != "datetime" && type != "float" && type != "money" && type != "tinyint" && type != "bigint" && type != "ntext" && type != "bit")
                            sqlstr = sqlstr + length;
                            if (isnull == "否")
                                sqlstr = sqlstr + " NOT NULL ";
                            if (isprimarykey == "是")
                                sqlstr = sqlstr + " PRIMARY KEY ";
                            sqlstr = sqlstr + ",";
                            if (i + j== ds.Tables[0].Rows.Count-1)
                            {
                                sqlstr = sqlstr.Substring(0, sqlstr.Length - 1);
                                sqlstr = sqlstr + ")";
                                SqlConnection objcon = new SqlConnection(constr);
                                objcon.Open();
                                SqlCommand objcom = new SqlCommand(sqlstr, objcon);
                                objcom.CommandTimeout = 60000;
                                objcom.ExecuteNonQuery();
                                objcon.Close();
                            }

                        }
                    }
                }
            }

        }

        private void bgWCreatetb_DoWork(object sender, DoWorkEventArgs e)
        {
            createTb();
        }

        private void bgWCreatetb_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {

        }

    }
}