（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学习笔记（五）,布布扣,bubuko.com
ADO.NET学习笔记（五）