小白学语句：SQL笔记5

练习4
数据的导入和导出
PS：前面要复制那段神奇的代码到Program.cs
首先建一个窗口程序，有button1（导入）、button2（导出）、ofdInport（打开文件对话框）、sfdOutport（保存文件对话框）

导入的代码如下：

private void button1_Click(object sender, EventArgs e)
        {
            if(ofdInport .ShowDialog()!=DialogResult.OK)
            {
                return;
            }
            using (FileStream fileStream = File.OpenRead(ofdInport.FileName))
            {
                using (StreamReader streamReader = new StreamReader(fileStream))
                {
                    using (SqlConnection conn = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=D:\C#\TheFirstmdf\数据导入导出\T_InOut.mdf;Integrated Security=True"))
                    {
                        conn.Open();
                        using (SqlCommand cmd = conn.CreateCommand())
                        {
                            cmd.CommandText = "insert into T_UserInOut(UserName,Age)values(@Name,@Age)";
                            string line = null;
                            while ((line =streamReader .ReadLine ())!=null)
                            {
                                string[] strs = line.Split('|');
                                string name = strs[0];
                                int age = Convert.ToInt32(strs[1]);
                                cmd.Parameters.Clear();//参数不能重复添加，在while中一直用的是一个SqlCommand对象
                                cmd.Parameters.Add(new SqlParameter("Name", name));
                                cmd.Parameters.Add(new SqlParameter("Age", age));
                                cmd.ExecuteNonQuery();
                            }
                        }
                    }
                }
                MessageBox.Show("导入成功！");
            }
        }

首先判断打开文件对话框返回的结果，如果不是OK就直接返回，是OK就才继续下面的操作
if(ofdInport .ShowDialog()!=DialogResult.OK)
{
return;
}
1、读取文件
using (FileStream fileStream = File.OpenRead(ofdInport.FileName))
2、获取文件内容
using (StreamReader streamReader = new StreamReader(fileStream))
3、建立数据库接连接
using (SqlConnection conn = new SqlConnection(@”Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=D:\C#\TheFirstmdf\数据导入导出\T_InOut.mdf;Integrated Security=True”))
4、创建与数据库的对话

using (SqlCommand cmd = conn.CreateCommand())
                        {
                            cmd.CommandText = "insert into T_UserInOut(UserName,Age)values(@Name,@Age)";
                            string line = null;
                            while ((line =streamReader .ReadLine ())!=null)
                            {
                                string[] strs = line.Split('|');
                                string name = strs[0];
                                int age = Convert.ToInt32(strs[1]);
                                cmd.Parameters.Clear();//参数不能重复添加，在while中一直用的是一个SqlCommand对象
                                cmd.Parameters.Add(new SqlParameter("Name", name));
                                cmd.Parameters.Add(new SqlParameter("Age", age));
                                cmd.ExecuteNonQuery();
                            }
                        }

读取文件每一行，一直读到行是空值的时候跳出
while ((line =streamReader .ReadLine ())!=null)
读取到的每一行内容，再通过cmd.CommandText的insert into写进数据库
读取字符的时候以”|”为分隔string[] strs = line.Split(‘|’);

导出的代码如下
导出的代码视频中没有写出，StreamWriter的用法是百度出来

private void button2_Click(object sender, EventArgs e)
        {
            if (sfdOutport.ShowDialog() != DialogResult.OK)
            {
                return;
            }
            using (FileStream fileStream = File.OpenWrite(sfdOutport.FileName))
            {
                using (StreamWriter streamWriter = new StreamWriter(fileStream))
                {
                    using (SqlConnection conn = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=D:\C#\TheFirstmdf\数据导入导出\T_InOut.mdf;Integrated Security=True"))
                    {
                        conn.Open();
                        using (SqlCommand cmd = conn.CreateCommand())
                        {
                            cmd.CommandText = "select UserName,Age from T_UserInOut";
                            using (SqlDataReader reader = cmd.ExecuteReader())
                            {
                                while (reader.Read ())
                                {
                                    string username = reader.GetString(reader.GetOrdinal("UserName"));
                                    int age = reader.GetInt32(reader.GetOrdinal("Age"));
                                    streamWriter.WriteLine("{0}|{1}", username, age);
                                }
                            }
                        }
                    }
                }
                MessageBox.Show("导出成功！");
            }
        }