ADO.NET学习笔记（五）

（3）数据导入导出数据库：

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

using System.Data.SqlClient;

using System.IO;

using System.Configuration;

namespace 数据导入导出

{

    public partial class Form1 : Form

    {

        public Form1()

        {

            InitializeComponent();

        }



        private void odfImport_Click(object sender, EventArgs e)

        {   //含中文的数据库字段如密码，昵称等要用nvalchar(50),n表示包含各种字符

            //注意要点：导入的中文前要都加N，如：insert into T_user(Name,NickName) values(‘zhx‘,N‘朱红星‘)

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

            {

                return;

            }

            using (FileStream fileStream = File.OpenRead(odfImport.FileName))

            {

                using (StreamReader streamReader = new StreamReader(fileStream, System.Text.ASCIIEncoding.Default))

                {//创建连接是非常耗时的，因此不要每次操作都创建连接

                    using (SqlConnection conn = new SqlConnection(@"Data Source = .\SQLEXPRESS;AttachDBFilename = |DataDirectory|\Database1.mdf;Integrated Security = True;  User Instance =True"))

                    {

                        conn.Open();

                        using (SqlCommand cmd = conn.CreateCommand())

                        {

                            string line = null;

                            while ((line = streamReader.ReadLine()) != null)

                            {

                                string[] strs = line.Split(‘|‘);

                                string name = strs[0];

                                int age = Convert.ToInt32(strs[1]);

                                cmd.CommandText = "insert into T_Persons(Name,Age) values(@Name,@Age)";

                                cmd.Parameters.Clear();//参数不能重复添加，在while中一直就用的一个SqlCommand，即一个Cmd对象(容器)被两次赋值，导致参数过多，应该每次赋值后清空罐子

                                cmd.Parameters.Add(new SqlParameter("Name", name));

                                cmd.Parameters.Add(new SqlParameter("Age", age));

                                cmd.ExecuteNonQuery();

                            }

                        }

                    }

                }

            }

            MessageBox.Show("导入成功！");

            /*

              if (odfImport.ShowDialog() == DialogResult.OK)

              { 



                  //如果第一次敲，没有提示就直接先敲出来，然后右键解析把包含的using 进来..........

              using(FileStream fileStream = File.OpenRead(odfImport.FileName))

              {

                  using (StreamReader streamReader = new StreamReader(fileStream))

                  {

                      string line = null;

                      while( (line = streamReader.ReadLine()) != null)

                      {

                          string[] strs = line.Split(‘|‘);

                          string name= strs[0];

                          int age = Convert.ToInt32(strs[1]);

                          using (SqlConnection conn = new SqlConnection(@"Data Source = .\SQLEXPRESS;AttachDBFilename = |DataDirectory|\Database1.mdf;Integrated Security = True;  User Instance =True"))//实现了IDisposable接口，用using括起，便于自动释放,在using()后调用了IDisposabl方法，它先判断有无conn.Close();如果没有，先进行关闭，在释放

                          {

                              conn.Open();

                              using (SqlCommand cmd = conn.CreateCommand())//创建命令对象的实例并与先建的数据库建立连接，将连接using进来，使此方法直接不用释放，出了括号会自动释放

                              {



                                  cmd.CommandText = "insert into T_Persons(Name,Age) values(@Name,@Age)";

                                  cmd.Parameters.Add(new SqlParameter("Name",name));

                                  cmd.Parameters.Add(new SqlParameter("Age", age));

                                  cmd.ExecuteNonQuery();



                              }

                          }

                       }

                    }

                 }

              }

              MessageBox.Show("导入成功！");

             */

        }



        private void ofdExport_Click(object sender, EventArgs e)

        {

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

            {

                return;

            }

            using (FileStream filestream = File.OpenWrite(odfImport.FileName))//文件流写入,关键一步:打开现有文件写入

            {

                using (StreamWriter streamWrite = new StreamWriter(filestream,System.Text.Encoding.Default))//文件流写入

                {

                    using (SqlConnection conn = new SqlConnection(@"Data Source = .\SQLEXPRESS;AttachDBFilename = |DataDirectory|\Database1.mdf;Integrated Security = True;  User Instance =True"))

                    {

                        conn.Open();

                        using (SqlCommand cmd = conn.CreateCommand())

                        {

                            cmd.CommandText = "select * from T_Persons";

                            using (SqlDataReader reader = cmd.ExecuteReader())

                            {

                                while (reader.Read())

                                {

                                    string Name = reader.GetString(reader.GetOrdinal("Name"));

                                    string Age = reader.GetString(reader.GetOrdinal("Age"));

                                    streamWrite.WriteLine("{0}|{1}", Name, Age); //格式化写入，类似Console.WriteLine();

                                }

                            }

                        }

                    }

                }

            }

            MessageBox.Show("导出成功~");

        }

    }

}

ADO.NET学习笔记（五）,古老的榕树,5-wow.com