ADO.NET学习笔记（五）

来源：互联网发布：淘宝联盟部分退款返利编辑：程序博客网时间：2024/06/09 07:26
（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("导出成功~");        }    }}
0 0