数据导入导出:
导入:从文本文件中读取数据,插入到数据库表中;
导出:从数据库中读取数据,保存到文本文件中。
(易错点:Parameter的重复添加)
应用程序的主入口点:
static void Main()
{
string dataDir = AppDomain.CurrentDomain.BaseDirectory;
if (dataDir.EndsWith(@"\bin\Debug\") || dataDir.EndsWith(@"\bin\Release\"))
{
dataDir = System.IO.Directory.GetParent(dataDir).Parent.Parent.FullName;
AppDomain.CurrentDomain.SetData("DataDirectory", dataDir);
}
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
Application.Run(new Form1());
}
导入到数据库表中(方法一:比较耗时):
private void btnImport_Click(object sender, EventArgs e)
{
OpenFileDialog dialog = new OpenFileDialog();
if (dialog.ShowDialog() == DialogResult.OK)
{
string filePath = dialog.FileName;
using (FileStream file = File.OpenRead(filePath))
{
using (StreamReader sr = new StreamReader(file,Encoding.Default))
{
string line = null;
while ((line = sr.ReadLine()) != null)
{
string[] strs = line.Split('|');
string name = strs[0];
int age = Convert.ToInt32(strs[1]);
int score = Convert.ToInt32(strs[2]);
using (SqlConnection conn = new SqlConnection(
@"Data Source =.\SQLEXPRESS; AttachDBFilename=|DataDirectory|\MyDB1.mdf; Integrated Security=True;User Instance=True"))
//创建连接是非常耗时的,因此不要每次操作都去创建连接
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "insert into T_Student(FName,FAge,FScore) values(@FName,@FAge,@FScore)";
cmd.Parameters.Add("FName", name);
cmd.Parameters.Add("FAge", age);
cmd.Parameters.Add("FScore", score);
cmd.ExecuteNonQuery();
}
}
}
}
MessageBox.Show("导入成功!");
}
}
}
上面程序比较耗时,因为在while中,每处理一条数据就要创建一次连接。
优化过后:
private void btnImport_Click(object sender, EventArgs e)
{
OpenFileDialog openDialog= new OpenFileDialog();
if (openDialog.ShowDialog() != DialogResult.OK)
{
return;
}
string filePath = openDialog.FileName;
using (FileStream file = File.OpenRead(filePath))
{
using (StreamReader sr = new StreamReader(file, Encoding.Default))
{
using (SqlConnection conn = new SqlConnection(
@"Data Source =.\SQLEXPRESS; AttachDBFilename=|DataDirectory|\MyDB1.mdf; Integrated Security=True;User Instance=True"))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
//采用参数化查询
cmd.CommandText = "insert into T_Student(FName,FAge,FScore) values(@FName,@FAge,@FScore)";
string line = null;
while ((line = sr.ReadLine()) != null)
{
string[] strs=line.Split('|');
string name = strs[0];
int age = Convert.ToInt32(strs[1]);
int score=Convert.ToInt32(strs[2]);
//每一次都要清空下参数集合
cmd.Parameters.Clear(); //参数不能重复,在while中一直都是用的同一个SqlCommand
cmd.Parameters.Add("FName", name);
cmd.Parameters.Add("FAge", age);
cmd.Parameters.Add("FScore", score);
cmd.ExecuteNonQuery();
}
}
}
}
}
MessageBox.Show("导入成功");
}
导出到文本文件中
private void btnExport_Click(object sender, EventArgs e)
{
SaveFileDialog saveDialog = new SaveFileDialog();
if (saveDialog.ShowDialog() != DialogResult.OK)
{
return;
}
string filePath = saveDialog.FileName;
using (FileStream file = File.OpenWrite(filePath))
{
using (StreamWriter sw = new StreamWriter(file))
{
using (SqlConnection conn = new SqlConnection(
@"Data Source =.\SQLEXPRESS; AttachDBFilename=|DataDirectory|\MyDB1.mdf; Integrated Security=True;User Instance=True"))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "select * from T_Student";
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
string name = reader.GetString(reader.GetOrdinal("FName"));
int age = reader.GetInt32(reader.GetOrdinal("FAge"));
int score = reader.GetInt32(reader.GetOrdinal("FScore"));
sw.WriteLine(string.Format("{0}|{1}|{2}",name,age,score));//写到流中
}
}
}
}
}
}
MessageBox.Show("导出成功!");
}