C#创建数据库和表并把读取到的csv数据存入新建表中

主要代码如下：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.IO;

namespace CreateSQLServe
{
  class Program
  {
    static void Main(string[] args)
    {
      string connString = "server = .; database =; uid = sa; pwd = !!123abc";
      //if(conn.State!=System.Data.ConnectionState.Open)
      string SQL= File.ReadAllText(GetPath("CreatServe.sql"));
      Log("++++"+ SQL + "+++++");
      SqlConnection sConn = new SqlConnection(connString);

      DataSet dtSet = new DataSet();

      try
      {
        sConn.Open();
      }
      catch (Exception ex)
      {
        Log("error：" + ex.Message);
      }
      SqlCommand sCmd = new SqlCommand(SQL, sConn);

      SqlDataAdapter sqlAdapter = new SqlDataAdapter(sCmd);
      string ifExist = "select * from sys.databases where name='RiderStatistics'";
      SqlCommand ifExistsCmd = new SqlCommand(ifExist, sConn);
      object n = ifExistsCmd.ExecuteScalar();
      if (n != null)
      {
        Log("++++++++数据库已存在++++++++");
      }
      else
      {
        sqlAdapter.Fill(dtSet);
      }
      sConn.Close();
      string CreateTableConn = "server = .; database =RiderStatistics; uid = sa; pwd = !!123abc";
      //if(conn.State!=System.Data.ConnectionState.Open)
      string CreateSQL = File.ReadAllText(GetPath("CreateTable.sql"));
      Log("++++" + CreateSQL + "+++++");
      SqlConnection sConnCreateTable = new SqlConnection(CreateTableConn);
      try
      {
        sConnCreateTable.Open();
      }
      catch (Exception ex)
      {
        Log("error：" + ex.Message);
      }
      DataSet dtSetCreateTable = new DataSet();
      SqlCommand sCmdCreateTable = new SqlCommand(CreateSQL, sConnCreateTable);
      SqlDataAdapter sqlAdapterCreateTable  = new SqlDataAdapter(sCmdCreateTable);

      string ifExistTable = " select* from sysobjects where name = 'RiderStatist'";
      SqlCommand ifExistTableCmd = new SqlCommand(ifExistTable, sConnCreateTable);
      object s = ifExistTableCmd.ExecuteScalar();
      if (s != null)
      {
        Log("++++++++表已存在++++++++");
      }
      else
      {
        sqlAdapterCreateTable.Fill(dtSetCreateTable);
      }
      string yesterdayfileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "output", DateTime.Now.Date.AddDays(-1).ToString("yyyy-MM-dd").ToString() + "_RidersUpdateVersion.csv");





      DataTable table = OpenCSV(yesterdayfileName);
      SqlConnection con = new SqlConnection("server = .; database =RiderStatistics; uid = sa; pwd = !!123abc");//连接数据库
      con.Open();
      SqlTransaction trans = con.BeginTransaction();//事物对象 
      try
      {
        SqlCommand com = new SqlCommand();//数据操作对象  
        com.Connection = con;//指定连接  
        com.Transaction = trans;//指定事物

        for (int i = 0; i < table.Rows.Count; i++)
        {  //对datatable循环    
          string sql = "INSERT INTO [RiderStatist]([date],[OldVersionActiveUserNum],[NewVersionActiveUserNum],[NewVersionInstallTotal],[NewVersionInstallAdd],[NewVersionInstallYesterday],[OldVersionPayNum],[NewVersionPayNum])values ('" + table.Rows[i]["日期"].ToString() + "','" + table.Rows[i]["老版本活跃用户数"] + "','" + table.Rows[i]["新版本活跃用户数"] + "','" + table.Rows[i]["新版本安装量（Total）"] + "','" + table.Rows[i]["新版本安装量(增量）"] + "','" + table.Rows[i]["新版本安装量(前一天）"] + "','" + table.Rows[i]["老版本支付笔数"] + "','" + table.Rows[i]["新版本支付笔数"] + "')";//某一行的数据
          com.CommandText = sql;
          com.ExecuteNonQuery();//执行该行   

        }
        trans.Commit();//如果全部执行完毕.提交
      }
      catch
      {
        trans.Rollback();//如果有异常.回滚.  
      }
      finally
      {
        con.Close();//关闭连接 
      }


    }
    private static string GetPath(string fileName)
    {
      return Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "SQLServe", fileName);
    }
    static void Log(string msg)
    {
      Console.WriteLine(msg);
      string path = @"log\log" + DateTime.Now.ToString("yyyy-MM-dd").ToString() + ".txt";
      msg = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.ffff") + "\t" + msg + "\r\n";
      File.AppendAllText(path, msg);
    }
    /// <summary>
     /// 将CSV文件的数据读取到DataTable中
     /// </summary>
     /// <param name="fileName">CSV文件路径</param>
     /// <returns>返回读取了CSV数据的DataTable</returns>
     public static DataTable OpenCSV(string filePath)
     {

         DataTable dt = new DataTable();
         FileStream fs = new FileStream(filePath, System.IO.FileMode.Open, System.IO.FileAccess.Read);

         //StreamReader sr = new StreamReader(fs, Encoding.UTF8);
         StreamReader sr = new StreamReader(fs, System.Text.Encoding.Default);
         //记录每次读取的一行记录
         string strLine = "";
         //记录每行记录中的各字段内容
         string[] aryLine = null;
         string[] tableHead = null;
         //标示列数
         int columnCount = 0;
         //标示是否是读取的第一行
         bool IsFirst = true;
         //逐行读取CSV中的数据
         while ((strLine = sr.ReadLine()) != null)
         {


             if (IsFirst == true)
             {
                 tableHead = strLine.Split(',');
                 IsFirst = false;
                 columnCount = tableHead.Length;
                 //创建列
                 for (int i = 0; i<columnCount; i++)
                 {
                     DataColumn dc = new DataColumn(tableHead[i]);
                     dt.Columns.Add(dc);
                 }
             }
             else
             {
                 aryLine = strLine.Split(',');
                 DataRow dr = dt.NewRow();
                 for (int j = 0; j<columnCount; j++)
                 {
                     dr[j] = aryLine[j];
                 }
                 dt.Rows.Add(dr);
             }
         }
         if (aryLine != null && aryLine.Length > 0)
         {
             dt.DefaultView.Sort = tableHead[0] + " " + "asc";
         }

         sr.Close();
         fs.Close();
         return dt;
     }
  }
}

创建数据库的sql

--新建数据可  
create database RiderStatistics  

创建表的sql

  create table RiderStatist
(  
    date  datetime,  
    OldVersionActiveUserNum  int,  
    NewVersionActiveUserNum  int,  
    NewVersionInstallTotal int,  
    NewVersionInstallAdd int,  
    NewVersionInstallYesterday int,  
    OldVersionPayNum int,
    NewVersionPayNum int,
)  

主要代码就是这了

1、导入命名空间

using System.Data.SqlClient; //连接SQLServer 数据库专用

2、创建连接

SqlConnection lo_conn = New SqlConnection(“Server=服务器名字或IP;Database=数据库名字;uid=用户名;pwd=密码”);

3、打开连接，第2步并没有真正连接数据库

lo_conn.Open(); //真正与数据库连接

4、向数据库发送SQL命令要使用SqlCommand:

SqlCommand lo_cmd = new SqlCommand(); //创建命令对象

lo_cmd.CommandText = “这里是SQL语句”; //写SQL语句

lo_cmd.Connection = lo_con; //指定连接对象，即上面创建的

5、处理SQL命令或返回结果集

lo_cmd.ExecuteNonQuery(); //这个仅仅执行SQL命令，不返回结果集，实用于建表、批量更新等不需要返回结果的操作。

SqlDataReader lo_reader = lo_cmd.ExecuteReader();//返回结果集

6、以数据集的方式反回结果集

SqlDataAdapter dbAdapter = new SqlDataAdapter(lo_cmd); //注意与上面的区分开

DataSet ds = new DataSet(); //创建数据集对象

dbAdapter.Fill(ds); //用返回的结果集填充数据集，这个数据集可以被能操作数据的控件DataBind

7、关闭连接

lo_conn.Close();