数据库的数据导入和导出

数据库的数据导入和导出

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace 数据导入导出
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        private void bt数据导入_Click(object sender, EventArgs e)
        {
            using (OpenFileDialog ofd = new OpenFileDialog())//创建一个OpenFileDialog实例
            {
                if (ofd.ShowDialog() != DialogResult.OK)//判断是否打开文件
                {
                    MessageBox.Show("错误。");//没打开文件提示
                    return;//退出
                }
                using (FileStream fs =File.OpenRead(ofd.FileName))//打开读取文件
                {
                    using (StreamReader sr = new StreamReader(fs))//读取文本
                    {
                            using (SqlConnection conn = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDBFilename=|DataDirectory|\sjk.mdf;integrated Security=True;User Instance=false"))//链接数据库
                            {
                                conn.Open();//打开数据库
                                using (SqlCommand cmd = conn.CreateCommand())//执行SQL语句
                                {
                                        cmd.CommandText = "insert into 用户(姓名,年龄) values(@name,@age)";//SQL语句
                                        string s = null;//定义一个变量
                                        while ((s = sr.ReadLine()) != null)//判断读取出的数据是否为null
                                        {
                                            string[] strs = s.Split(new string[] {"|"},StringSplitOptions.RemoveEmptyEntries);//分割读取的数据保存到数组
                                            cmd.Parameters.Clear();//清除
                                            cmd.Parameters.Add(new SqlParameter("name",strs[0]));//添加
                                            cmd.Parameters.Add(new SqlParameter("age",strs[1]));//添加
                                            cmd.ExecuteNonQuery();//执行语句
                                        }
                                    }
                            }
                    }
                }
            }
            MessageBox.Show("导入成功。");//提示
        }
        private void bt数据导出_Click(object sender, EventArgs e)
        {
            using (SqlConnection conn =new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDBFilename=|DataDirectory|\sjk.mdf;integrated Security=True;User Instance=false"))
            {//链接数据库
                conn.Open();//打开数据
                using (SqlCommand cmd = conn.CreateCommand())//执行SQL语句
                {
                    using (SaveFileDialog sfd = new SaveFileDialog()) //创建一个 另存为对话框
                    {
                        sfd.Filter = "文本文件(*.txt)|*.txt|所有文件(*.*)|*.*"; //另存为的类型
                        sfd.FilterIndex = 1; //默认显示另存为类型序号
                        sfd.FileName = "保存";//默认保存文件名字
                        sfd.DefaultExt = "txt";// 默认保存文件类型  可省略不写
                        sfd.AddExtension = true; //设置自动在文件名中添加扩展名
                        if (sfd.ShowDialog() != DialogResult.OK)//判断是否另存为文件
                        {
                            MessageBox.Show("请输入要保存文件信息。");//没另存为提示
                            return;//退出
                        }
                            using (StreamWriter sw = new StreamWriter(sfd.FileName, true, Encoding.Default)) //读取文件
                            {
                                cmd.CommandText = "select 姓名,年龄 from 数据";//SQL语句
                                using (SqlDataReader dr = cmd.ExecuteReader())//保存从数据库搜索的结构
                                {
                                    while (dr.Read())//读取
                                    {
                                    sw.WriteLine(dr.GetString(dr.GetOrdinal("姓名"))+"|"+dr.GetInt32(dr.GetOrdinal("年龄")));//保存信息
                                    }
                                }
                            }
                    }
                }
            }
            MessageBox.Show("导出成功.");//提示.
        }
    }
}

导入输出结果：

导出输入结果：