步步为营-39-数据的导入导出

说明:数据的导入导出功能在企业中还是比较常用的,下面先介绍txt文件的导入导出,但是企业中一般是用Excel导入导出

1 先建UI页面　　

2清空数据库数据

3 导入数据

4导出数据

代码

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;

using System.Threading.Tasks;
using System.Windows.Forms;
using Microsoft.Win32.SafeHandles;

namespace UserLogin
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        //导入
        private void btnImport_Click(object sender, EventArgs e)
        {
            //1、找到文件--这里使用OpenfileDialog类
            using (OpenFileDialog ofd = new OpenFileDialog())
            {
                //1-01设置标题
                ofd.Title = "请选择要导入的文件";
                //1-02设置初识路径
                ofd.InitialDirectory = @"C:UsershomeDesktop";
                //1-03 设置文件类型
                ofd.Filter = "文本文件|*.txt";
                if (ofd.ShowDialog() == DialogResult.OK)
                {
                    this.txtSelectFile.Text = ofd.FileName;

                    //导入数据
                    ImportData(ofd.FileName);
                    MessageBox.Show("导入成功");
                }
            }


        }
        //导出
        private void btnExport_Click(object sender, EventArgs e)
        {
            //1、找到文件--这里使用OpenfileDialog类
            using (OpenFileDialog ofd = new OpenFileDialog())
            {
                //1-01设置标题
                ofd.Title = "请选择要导出的文件路径";
                //1-02设置初识路径
                ofd.InitialDirectory = @"C:UsershomeDesktop";
                //1-03 设置文件类型
                ofd.Filter = "文本文件|*.txt";
                if (ofd.ShowDialog() == DialogResult.OK)
                {
                    this.txtExport.Text = ofd.FileName;
                    //导入数据
                    ExportData();
                    MessageBox.Show("导出成功");
                }
            }
          
        }
        private void ImportData(string fileName)
        {
            string temp = string.Empty;
            //2、读取文件
            using (StreamReader reader = new StreamReader(fileName,Encoding.UTF8))
            {
                reader.ReadLine();//去掉第一行
                
                //32-01连接数据库字符串
                string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
                //3-02创建连接对象
                SqlConnection conn = new SqlConnection(connStr);
                using (conn)
                {
                    //3-03创建数据库执行对象
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = conn;
                    conn.Open();
                    using (cmd)
                    {
                        //如果下一行数据不为空,继续执行
                        while (!string.IsNullOrEmpty(temp = reader.ReadLine()))
                        {
                            string[] strs = temp.Split(new string[]{"	"}, StringSplitOptions.RemoveEmptyEntries);
                            //创建sql语句
                            cmd.CommandText = string.Format("insert into UserInfo ( Pwd, StuName, StuAge, Delflag, ClassNo) values ({0},'{1}',{2},{3},{4})", strs[1], strs[2], strs[3], strs[4], strs[5]);
                            cmd.ExecuteNonQuery();
                        }
                    }

                }
            }
            
        }

        private void ExportData()
        {
            //连接数据库字符串
            //32-01连接数据库字符串
            string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
            //3-02创建连接对象
            SqlConnection conn = new SqlConnection(connStr);
            using (conn)
            {

                conn.Open();

                using (System.Data.SqlClient.SqlCommand command = conn.CreateCommand())
                {
                    command.CommandText = "select * from UserInfo";
                    using (System.Data.SqlClient.SqlDataAdapter adp = new System.Data.SqlClient.SqlDataAdapter(command))
                    {
                        System.Data.DataTable dt = new System.Data.DataTable();
                        adp.Fill(dt);
                        DataRow[] myRow = dt.Select();
                        int cl = dt.Columns.Count;
                        if (cl <= 0)
                        {
                            MessageBox.Show("数据不存在");
                        }

                        using (FileStream fsWrite= new FileStream(txtExport.Text.Trim(),FileMode.OpenOrCreate,FileAccess.Write))
                        {
                            #region 写数据

                            //逐行写入数据
                            foreach (DataRow row in myRow)
                            {
                                string ls_item = string.Empty;
                                for (int i = 0; i < cl; i++)
                                {
                                    if (i == (cl - 1))
                                    {
                                        ls_item += row[i].ToString() + "
";
                                    }
                                    else
                                    {
                                        ls_item += row[i].ToString() + "	";
                                    }
                                   
                                }
                                byte[] buffer = Encoding.UTF8.GetBytes(ls_item);

                                fsWrite.Write(buffer, 0, buffer.Length);

                            }
                            #endregion     
                        }
                       
                    }
                }
              
            }
        }

       
    }
}