调用代码：
string tableName = "Sheet1";
openFileDlg.ShowDialog();
DataTable dt = GeneralFun.FileToDataTable(openFileDlg.FileName, tableName);//将文件转换成对象
dataGridView1.DataSource = dt; String desConnString = ConnSql.GetConnStr("192.168.1.61", "sa", "bdyh", "tm_base_sys", ""); GeneralFun.DataTableToSql(dt, tableName, desConnString);
操作SQL数据库类：
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Collections; /// <summary>
/// SQL数据库操作类
/// zouhao
/// 2011.5.10
/// </summary>
public class ConnSql
{
//获得数据库连接字符串
public static string connString = "";
public static string GetConnStr(string serverName, string userId, string password, string dbName)
{
connString = @"Persist Security Info=False;User ID=" + userId + ";Password=" + password + ";Initial Catalog=" + dbName + ";Server=" + serverName;
return connString;
} public static string GetConnStr(string serverName, string userId, string password, string dbName, string timeOut)
{
connString = @"Persist Security Info=False;User ID=" + userId + ";Password=" + password + ";Initial Catalog=" + dbName + ";Server=" + serverName + ";Connection Timeout=" + timeOut;
return connString;
} /// <summary>
/// 判断数据库是否连接成功
/// </summary>
/// <returns>true</returns>
public static bool Connect()
{
return Connect(connString);
} /// <summary>
/// 判断数据库是否连接成功
/// </summary>
/// <param name="_connstring"></param>
/// <returns>true</returns>
public static bool Connect(string _connString)
{
using (SqlConnection con = new SqlConnection(_connString))
{
try
{
con.Close();
con.Open();
return true;
}
catch (Exception e)
{
e.Message.ToString();
con.Close();
//throw new Exception(e.Message);
return false; }
finally
{
con.Close();
}
} } /// <summary>
/// 打开数据集
/// </summary>
/// <param name="sql"></param>
/// <returns>数据集DataTable</returns> public static DataTable Open(string sql)
{
return Open(sql, connString);
} /// <summary>
/// 打开数据集
/// </summary>
/// <param name="sql"></param>
/// <param name="_connstring"></param>
/// <returns>数据集DataTable</returns>
///
public static DataTable Open(string sql, string _connString)
{ DataTable dt = new DataTable();
if (sql.Equals(""))
{
return null;
}
using (SqlConnection con = new SqlConnection(_connString))
{
try
{
con.Close();
con.Open();
SqlCommand cmd = new SqlCommand(sql,con);
//SqlCommand cmd = new SqlCommand(sql, con);
SqlDataAdapter oda = new SqlDataAdapter(cmd);
oda.Fill(dt);
cmd.Dispose();
return dt;
}
catch (Exception e)
{
con.Close();
//return null;
throw new Exception(e.Message);
}
finally
{
con.Close();
}
}
} /// <summary>
/// 返回查询结构集个数
/// </summary>
/// <param name="sql"></param>
/// <param name="_connstring"></param>
/// <returns>结果集个数</returns> public static int RecordCount(string sql)
{
return RecordCount(sql, connString);
} /// <summary>
/// 返回查询结果集个数
/// </summary>
/// <param name="sql"></param>
/// <param name="_connstring"></param>
/// <returns>结果集个数</returns>
///
public static int RecordCount(string sql, string _connString)
{
DataTable dt = new DataTable();
using (SqlConnection con = new SqlConnection(_connString))
{
try
{
con.Close();
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
SqlDataAdapter oda = new SqlDataAdapter(cmd);
oda.Fill(dt);
cmd.Dispose();
con.Close();
return dt.Rows.Count;
}
catch (Exception e)
{
con.Close();
//return -1;
throw new Exception(e.Message); }
finally
{
con.Close();
} }
} /// <summary>
/// 执行SQL语句
/// </summary>
/// <param name="sql"></param>
/// <returns>影响数据个数</returns> public static int Execute(string sql)
{
return Execute(sql, connString);
} /// <summary>
/// 执行SQL语句
/// </summary>
/// <param name="sql"></param>
/// <param name="_connstring"></param>
/// <returns>影响数据个数</returns>
public static int Execute(string sql, string _connString)
{
int count = ;
if (sql.Equals(""))
{
return -;
}
using (SqlConnection con = new SqlConnection(_connString))
{
try
{
con.Close();
con.Open(); SqlTransaction trans = con.BeginTransaction();
try
{ SqlCommand cmd = con.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText = sql;
count = cmd.ExecuteNonQuery(); trans.Commit();
return count;
}
catch (Exception e)
{
trans.Rollback();
count = -; //return count;
throw new Exception(e.Message); }
finally
{ con.Close(); }
}
catch (Exception e)
{
//trans.Rollback(); throw new Exception(e.Message);
//return -100;
}
}
} /// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">多条SQL语句</param>
public static void ExecuteSqlTran(ArrayList SQLStringList, string _connString)
{
using (SqlConnection conn = new SqlConnection(_connString))
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
SqlTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
for (int n = ; n < SQLStringList.Count; n++)
{
string strsql = SQLStringList[n].ToString();
if (strsql.Trim().Length > )
{
cmd.CommandText = strsql;
cmd.ExecuteNonQuery();
}
}
tx.Commit();
}
catch (Exception e)
{
tx.Rollback();
throw new Exception(e.Message);
}
}
} /// <summary>
/// 通过DataTable批量更新数据库
/// </summary>
/// <param name="newDT"></param>
/// <param name="sql"></param>
/// <returns>执行结果</returns> public static bool UpdateDT(DataTable newDT, string sql)
{
return UpdateDT(newDT, sql, connString);
}
/// <summary>
/// 通过DataTable批量更新数据库
/// </summary>
/// <param name="newDT"></param>
/// <param name="sql"></param>
/// <param name="_connString"></param>
/// <returns>执行结果</returns> public static bool UpdateDT(DataTable newDT, string queryString, string _connString)
{ using (SqlConnection connection = new SqlConnection(_connString))
{
try
{
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = new SqlCommand(queryString, connection);
SqlCommandBuilder builder = new SqlCommandBuilder(adapter); connection.Open(); //DataSet customers = new DataSet();
DataTable dt = new DataTable();
adapter.Fill(dt); //code to modify data in dataset here adapter.Update(newDT); return true;
}
catch (Exception e)
{
connection.Close();
//return ;
throw new Exception(e.Message);
}
finally
{
connection.Close();
}
}
} public static int SqlExecuteNonQuery(string sql)
{
return SqlExecuteNonQuery(sql, connString);
} public static int SqlExecuteNonQuery(string sql, string _connString)
{
using (SqlConnection con = new SqlConnection(_connString))
{
try
{
con.Close();
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
int i = cmd.ExecuteNonQuery(); return i;
}
catch (Exception e)
{
con.Close();
throw new Exception(e.Message);
}
finally
{
con.Close();
}
}
} public static object SqlExcuteScalar(string sql)
{
return SqlExcuteScalar(sql, connString);
} public static object SqlExcuteScalar(string sql, string _connString)
{
using (SqlConnection con = new SqlConnection(_connString))
{
try
{
con.Close();
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
object obj = cmd.ExecuteScalar(); return obj;
}
catch (Exception e)
{
con.Close();
throw new Exception(e.Message);
}
finally
{
con.Close();
}
}
} /// <summary>
/// SQLExecuteTransaction
/// </summary>
/// <param name="sqls"></param>
/// <returns>using try catch to catch the error msg</returns>
public static bool SQLExecuteTransaction(string[] sqls)
{
return SQLExecuteTransaction(sqls, connString);
}
public static bool SQLExecuteTransaction(string[] sqls, string _connString)
{
using (SqlConnection con = new SqlConnection(_connString))
{
try
{
con.Open();
SqlTransaction trans = con.BeginTransaction();
try
{ SqlCommand cmd = con.CreateCommand();
cmd.Transaction = trans; foreach (string s in sqls)
{
cmd.CommandText = s;
cmd.ExecuteNonQuery();
}
trans.Commit();
return true;
}
catch (Exception e)
{
trans.Rollback();
throw new Exception(e.Message);
}
finally
{
con.Close();
}
}
catch (Exception e)
{
//trans.Rollback();
throw new Exception(e.Message);
}
}
} /// <summary>
/// 判断指定表是否存在
/// </summary>
/// <param name="_connString">数据库连接字符串</param>
/// <param name="tableName">表名</param>
/// <returns></returns>
public static bool isTableExist(String tableName, string _connString)
{
//查询数据库中表 固定语句
String sql = "select * from sys.objects where type='U' and name='" + tableName + "'"; DataTable dt = new DataTable();
if (sql.Equals(""))
{
return false;
}
using (SqlConnection con = new SqlConnection(_connString))
{
try
{
con.Close();
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
SqlDataAdapter oda = new SqlDataAdapter(cmd);
oda.Fill(dt);
cmd.Dispose();
if (dt.Rows.Count > )
{
return true;
}
}
catch (Exception e)
{
con.Close();
}
finally
{
con.Close();
}
}
return false;
} /// <summary>
/// 利用DataTable的数据结构,在SQL中创建新表
/// </summary>
/// <param name="dt">数据表对象</param>
/// <param name="tableName">表名称</param>
/// <param name="_connString">连接数据库字符串</param>
/// <returns></returns>
public static bool CreateTableToSql(DataTable dt,string tableName, string _connString)
{
try
{
StringBuilder sb = new StringBuilder();
sb.Append("create table [" + tableName + "] (");
foreach (DataColumn column in dt.Columns)
{
sb.Append(" [" + column.ColumnName + "] " + ConnSql.GetTableColumnType(column.DataType) + ",");
}
string sql = sb.ToString();
sql = sql.TrimEnd(',');
sql += ")";
ConnSql.Execute(sql, _connString);
}
catch (Exception ex)
{
return false;
}
return true;
} /// <summary>
/// 将DataTable 数据类型转换成 SQL 支持的类型
/// </summary>
/// <param name="type">DataTable 列类型</param>
/// <returns></returns>
public static string GetTableColumnType(System.Type type)
{
string result = "varchar(8000)";
string sDbType = type.ToString();
switch (sDbType)
{
case "System.String":
break;
case "System.Int16":
result = "int";
break;
case "System.Int32":
result = "int";
break;
case "System.Int64":
result = "float";
break;
case "System.Decimal":
result = "decimal(18,6)";
break;
case "System.Double":
result = "decimal(18,6)";
break;
case "System.DateTime":
result = "datetime";
break;
default:
break;
}
return result;
} }
通用方法类(数据复制)：
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Data.SqlClient; namespace aaaaaa
{
/// <summary>
/// 通用函数类
/// 2013.10.09
/// zouhao
/// </summary>
class GeneralFun
{
/// <summary>
/// 从文件中(Excel、Access)读取数据,装载到DataTable对象
/// </summary>
/// <param name="pathName">绝对路径+文件名</param>
/// <param name="tableName">表名</param>
/// <returns></returns>
public static DataTable FileToDataTable(string pathName, string tableName)
{
return GeneralFun.FileToDataTable(pathName, tableName, "");
} /// <summary>
/// 从文件中(Excel、Access)读取数据,装载到DataTable对象
/// </summary>
/// <param name="pathName">绝对路径+文件名</param>
/// <param name="tableName">表名</param>
/// <param name="where">查询条件</param>
/// <returns></returns>
public static DataTable FileToDataTable(string pathName, string tableName, string where)
{
//格式化传入传输
pathName = pathName.Trim().ToLower();
tableName = tableName.Trim().ToLower();
where = where.Trim().ToLower(); //读取数据
DataTable tbContainer = new DataTable();
string strConn = string.Empty;
if (string.IsNullOrEmpty(tableName)) { tableName = "Sheet1"; }
FileInfo file = new FileInfo(pathName);
if (!file.Exists) { throw new Exception("文件不存在"); }
string extension = file.Extension.Trim().ToLower();
switch (extension)
{
case ".xls"://Excel2003
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=No;IMEX=1;'";
tableName += "$";
break;
case ".xlsx"://Excel2007
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pathName + ";Extended Properties='Excel 12.0;HDR=No;IMEX=1;'";//{IMEX = 0:写,1:读,2:读/写;} {HDR = Yes，第一行是标题}
tableName += "$";
break;
case ".mdb"://Access2003
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName;
break;
case ".accdb"://Access2007
strConn = "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" + pathName;
//Provider=Microsoft.Ace.OleDb.12.0;Data Source=文件位置;Jet OLEDB:Database Password=密码;
break;
}
//链接文件
OleDbConnection cnnxls = new OleDbConnection(strConn);
//生成SQL字符串
string sql = string.Format(" select * from [{0}] ", tableName);
//判断是否有条件
if (!string.IsNullOrEmpty(where))
{
//判读用户是否添加了 where 字符串
if (- == where.IndexOf("where")) where = " where " + where;
//添加查询条件
sql += where;
}
//读取文件数据
OleDbDataAdapter oda = new OleDbDataAdapter(sql, cnnxls);
DataSet ds = new DataSet();
//将文件里面有表内容装载到内存表中！
oda.Fill(tbContainer);
return tbContainer;
} /// <summary>
/// 将DataTable 覆盖到 SQL某表(包括表结构及所有数据)
/// </summary>
/// <param name="dt">数据表对象</param>
/// <param name="tableName">表名称</param>
/// <param name="desConnString">SQL数据库连接字符串。例：Persist Security Info=False;User ID=sa;Password=bdyh;Initial Catalog=tm_base_sys;Server=192.168.1.61;Connection Timeout=2</param>
/// <returns></returns>
public static bool DataTableToSql(DataTable dt, string tableName, string desConnString)
{
try
{
//判断连接是否成功
if (ConnSql.Connect(desConnString))
{
//1、判断服务器表是否存在,如果不存在则服务器端创建表
if (ConnSql.isTableExist(tableName, desConnString))
{
//MessageBox.Show(tableName + "表存在！"); //清除已存在的表,并且重新创建新表(好处在于,复制数据的时候,所有字段肯定符合要求,不容易出错)
{
//清除已存在的表
ConnSql.Execute("drop table " + tableName + "", desConnString);
//创建新表
ConnSql.CreateTableToSql(dt, tableName, desConnString);
} //或者 ////直接删除表数据,不重新建表(弊端在于,复制数据的时候,某些字段有可能不符合要求,容易出错)
//{
// //清除已存在的表
// ConnSql.Execute("delete from " + tableName + "", desConnString);
//}
}
else
{
//MessageBox.Show(tableName + "不表存在！"); //创建新表
ConnSql.CreateTableToSql(dt, tableName, desConnString); } //2、拷贝数据到服务器
using (SqlBulkCopy sqlCopy = new SqlBulkCopy(desConnString, SqlBulkCopyOptions.UseInternalTransaction))
{
sqlCopy.BulkCopyTimeout = ;
sqlCopy.DestinationTableName = tableName;
if (dt != null && dt.Rows.Count != )
{
sqlCopy.WriteToServer(dt);
}
}
}
else
{
throw new Exception("连接服务器失败！");
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
} return true;
}
}
}