主要代码如下:
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();