C#实现TXT文本数据批量导入SQL Server

来源：互联网发布：常州青之峰网络编辑：程序博客网时间：2024/05/01 10:05

用程序就是将文本文件的每行根据固定的分隔符用Split函数分割，然后逐个加到参数中去..

给个简单的Demo

SQL code
create database data
go
use data
go
create table custom
(
    客户名 varchar(20),
    联系方式 varchar(10),
    住址 varchar(50)
)

custome.txt
张三,135******,123456
李四,135******,XXXXXX

C# code
using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;

public class Test
{
    public static void Main()
    {
        StreamReader sr = new StreamReader(new FileStream("custom.txt", FileMode.Open, FileAccess.Read), System.Text.Encoding.Default);

SqlConnection cn = new SqlConnection("Server=.;uid=sa;pwd=;database=data");;

        string insertCmd = "insert into custom values (@name, @phone, @address)";

        char delimChar = ',';
        string [] split = null;

        string strTemp = sr.ReadLine();
        while(strTemp != null)
        {
            split = strTemp.Split(delimChar);
            SqlCommand cmd = new SqlCommand(insertCmd, cn);

            cmd.Parameters.Add("@name", SqlDbType.VarChar).Value = split[0];
            cmd.Parameters.Add("@phone", SqlDbType.VarChar).Value = split[1];
            cmd.Parameters.Add("@address", SqlDbType.VarChar).Value = split[2];
            cn.Open();
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch(System.Data.SqlClient.SqlException e)
            {
                Console.WriteLine(e.Message);
            }
            cn.Close();
            strTemp = sr.ReadLine();
        }
        sr.Close();
    }
}