用封装的方法实现从文件夹名下所有的指定类型文件数据导入到数据库

using System.Data.SqlClient;

using System.IO;

using System.Configuration;

 

namespace 用封装的方法实现从文件夹名下所有的指定类型文件数据导入到数据库表

{

 

    class  SqlHelper

    {

        //封装ExecuteNonQuery。  ExecuteNonQuery()返回的是int类型

        public static int ExecuteNonQuery (string sql,params SqlParameter[] parameter)

        {

            //ConfigurationManager类需要引用，选中项目名称后点右键-添加引用，在.NET下选中System.Configuration双击就可以了，然后using一下就可以了：using System.Configuration;

            string  getCon = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;

            using (SqlConnection conn =newSqlConnection(getCon))

            {

                conn.Open();

                using (SqlCommand cmd = conn.CreateCommand())

                {

                    cmd.CommandText = sql;

                    foreach (SqlParameter parametersin parameter)

                    {

                       cmd.Parameters.Add(parameters);

                    }

                    return cmd.ExecuteNonQuery()

                }

            }

        }

 

        // 封装ExecuteScalar 。  ExecuteScalar()返回的是Object类型

        public static object  ExecuteScalar (string sql,paramsSqlParameter[]par)

        {

            using ( SqlConnection conn=new SqlConnection (ConfigurationManager .ConnectionStrings ["ConnStr"].ConnectionString ))

            {

                using (SqlCommand cmd = conn.CreateCommand())

                {

                    cmd.CommandText = sql;

                    foreach (SqlParameter sparinpar)

                    {

                       cmd.Parameters.Add(spar);

                    }

                    return  cmd.ExecuteScalar();

                }

            }

        }

        // 实现查询的封装 DataReader不能用，因为conn连接一断开就无法读取数据。这里用DataTable实现封装

        public static DataTable  ExecuteDataTable(stringsql,paramsSqlParameter[]pars)

        {

            using(SqlConnection conn =newSqlConnection(ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString))

            {

                using(SqlCommand cmd = conn.CreateCommand())

                {

                    cmd.CommandText = sql;

                    foreach(SqlParameter  parin pars)

                    {

                       cmd.Parameters.Add(par);

                    }

                    DataSetds =newDataSet();

                    SqlDataAdapterda =newSqlDataAdapter(cmd);

                    da .Fill (ds );

                    returnds.Tables[0];                 

                }

            }

        }

    }

   

-------------------------------------------------------------------------------------------------------------------------------- 

--------------------------------------------------------------------------------------------------------------------------------

    public partial class Form1 : Form

    {

        publicForm1()

        {

            InitializeComponent();

        }

 

        FolderBrowserDialog控件,只允许用户选择文件夹而非文件

        privatevoid button1_Click(objectsender,EventArgs e)

        { 

            //FolderBrowserDialogdlg = new FolderBrowserDialog();

            //folderBrowserDialog1.ShowDialog()会弹出1个文件夹对话框，当关闭这个对话框时（选择确定或者取消）

            if(folderBrowserDialog1.ShowDialog() !=DialogResult.OK)

            {

                return;

            }

           //为防止重复导入数据到数据库表，先把表里的资料清除。

            SqlHelper.ExecuteNonQuery("delete from phone");

            MessageBox.Show("数据清除成功");

 

 

            //stringpath = dlg.SelectedPath;

            //string[]file = Directory.GetFiles(path,"*.txt", SearchOption.AllDirectories);

            string[]file = Directory.GetFiles(folderBrowserDialog1.SelectedPath,"*.txt",SearchOption.AllDirectories);

 

            foreach(string filesinfile)

            {

                string [] line = File.ReadAllLines(files, Encoding.Default);

 

                foreach (string strinline)

                {

 

                    string [] getline = str.Split(newchar[] {' ','\t' },StringSplitOptions.RemoveEmptyEntries);

                    string HaoDuan = getline[0];

                    string Area = getline[1];

                    string Operators = getline[2];

                    string AreaCode = getline[3];

 

                    SqlHelper.ExecuteNonQuery ("insert intophone(HaoDuan,Area,Operators,AreaCode)values(@HaoDuan,@Area,@Operators,@AreaCode)",newSqlParameter("HaoDuan", HaoDuan),newSqlParameter("Area", Area),newSqlParameter("Operators",Operators),newSqlParameter("AreaCode", AreaCode)); 

                } 

            }

            MessageBox.Show("数据导入成功");

 

 

            //测试一下 ExecuteDataTable()的封装

            DataTable dt = SqlHelper.ExecuteDataTable("select * from phone where area=@area",newSqlParameter("area","广东省广州市"));

          //用foreach()语句实现

            foreach(DataRow dwin dt.Rows)

            {

                MessageBox.Show(Convert.ToString("手机号段为：" + dw["haoduan"])+"；运营商：" + dw["Operators"]+"；区号是：" + dw["AreaCode"]);

            }

 

         //用for（）语句实现

            for(int i = 0; i < dt.Rows.Count; i++)

            {

                DataRowdw = dt.Rows[i];

                MessageBox.Show(Convert.ToString("手机号段为：" + dw["haoduan"])+ "；运营商：" + dw["Operators"]+ "；区号是：" + dw["AreaCode"]);

                // nt  haoduan =Convert .ToInt32 ( dw["haoduan"]);

                // MessageBox.Show(haoduan.ToString());

                // MessageBox.Show(Convert.ToString(dw["AreaCode"]));                

            }

        }

    }

}