ADO.Net_案例1(数据导入导出)

来源：互联网发布：外国人在淘宝网可以吗编辑：程序博客网时间：2024/05/17 20:35

数据导入导出：

导入：从文本文件中读取数据，插入到数据库表中；

导出：从数据库中读取数据，保存到文本文件中。

（易错点：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("导出成功!");
        }