调用代码:
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", "2"); 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 = 0; if (sql.Equals("")) { return -100; } 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 = -100; //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 = 0; n < SQLStringList.Count; n++) { string strsql = SQLStringList[n].ToString(); if (strsql.Trim().Length > 1) { 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 > 0) { 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 (-1 == 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 = 500000; sqlCopy.DestinationTableName = tableName; if (dt != null && dt.Rows.Count != 0) { sqlCopy.WriteToServer(dt); } } } else { throw new Exception("连接服务器失败!"); } } catch (Exception ex) { throw new Exception(ex.Message); } return true; } } }
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 开发者必知的日志记录最佳实践
· SQL Server 2025 AI相关能力初探
· Linux系列:如何用 C#调用 C方法造成内存泄露
· AI与.NET技术实操系列(二):开始使用ML.NET
· 记一次.NET内存居高不下排查解决与启示
· Manus重磅发布:全球首款通用AI代理技术深度解析与实战指南
· 被坑几百块钱后,我竟然真的恢复了删除的微信聊天记录!
· 没有Manus邀请码?试试免邀请码的MGX或者开源的OpenManus吧
· 园子的第一款AI主题卫衣上架——"HELLO! HOW CAN I ASSIST YOU TODAY
· 【自荐】一款简洁、开源的在线白板工具 Drawnix