C#用ADO.NET访问数据库v31
注意：
1、.net6下调试通过。
2、需要引入对应的包（System.Data.SqlClient、System.Data.OleDb、MySql.Data）。
3、localdb版本“13.0.1601.5"。
4、Access支持accdb和mdb（2002-2003），系统提示仅支持Windows系统，在Asp.Net MVC里也调试通过。
5、MySql暂未测试。
调用注意事项：
根据项目需求，建议采用依赖注入或静态变量，以持久化对象。
后期待完善：
1、可使用内部类和读写接口，整合在一起。
2、所有“CommandExecute”方法，连接的打开也应该放到try里。
SQLSERVER：
测试数据库d1，表t1：
CREATE TABLE [dbo].[t1]( [xm] [nvarchar](10) NULL, [nl] [tinyint] NULL ) ON [PRIMARY]
数据：
xm nl zs 20 ls 18 ww 19 t1 21 t2 22
SqlHelper.cs提供操作：
using System.Data; using System.Data.SqlClient; namespace WanJinLiuSoft.DBHelper { internal partial class SqlHelper { SqlConnection connection; SqlDataAdapter adapter; SqlCommand command; DataSet ds; public SqlHelper(string ip_add, string user_name, string user_pass, string db_name) { string s1 = $"server={ip_add};initial catalog={db_name};user ID={user_name};password={user_pass};"; connection = new SqlConnection(s1); adapter = new SqlDataAdapter(); adapter.SelectCommand = new SqlCommand(); adapter.SelectCommand.Connection = connection; command = new SqlCommand(); command.Connection = connection; ds = new DataSet(); } public DataTable 读(string sql) { ds.Clear(); adapter.SelectCommand.CommandText = sql; return AdapterRead(); } public DataTable 读_参数化(FormattableString sql) { ds.Clear(); set_Command(sql); adapter.SelectCommand = command; return AdapterRead(); } public string 写(string sql) { command.CommandText = sql; return CommandExecute(); } public string 写_参数化(FormattableString sql) { set_Command(sql); return CommandExecute(); } } }
SqlHelper_Function.cs提供辅助：
using System.Data; namespace WanJinLiuSoft.DBHelper { internal partial class SqlHelper { void set_Command(FormattableString x) { var t = x.Format; var args = x.GetArguments(); command.Parameters.Clear(); for (int i = 0; i < args.Length; i++) { t = t.Replace("{" + i + "}", $"@p{i}"); command.Parameters.AddWithValue($"p{i}", args[i]); } command.CommandText = t; } DataTable AdapterRead() { try { adapter.Fill(ds); return ds.Tables[0]; } catch (Exception e) { DataTable table = new DataTable(); DataColumn column; DataRow row; column = new DataColumn(); column.DataType = System.Type.GetType("System.String"); column.ColumnName = "Message"; table.Columns.Add(column); row = table.NewRow(); row[0] = e.Message; table.Rows.Add(row); return table; } } string CommandExecute() { connection.Open(); try { return command.ExecuteNonQuery().ToString(); } catch (Exception e) { return e.Message; } finally { connection.Close(); } } } }
调试代码：
static void Main(string[] args) { var t1 = "t2"; var t2 = 22; var db = new SqlHelper("(localdb)\\mssqllocaldb","sa","123456","d1"); Console.WriteLine(db.写("delete from t1 where xm='t1'")); Console.WriteLine(db.写_参数化($"delete from t1 where xm={t1}")); var dt = db.读_参数化($"select * from t1"); showTable(dt); Console.WriteLine(db.写("insert into t1 values('t1',21)")); Console.WriteLine(db.写_参数化($"insert into t1 values({t1},{t2})")); dt = db.读_参数化($"select * from t1"); showTable(dt); } static void showTable(System.Data.DataTable t) { for (int i = 0; i < t.Rows.Count; i++) { for (int j = 0; j < t.Columns.Count; j++) { Console.Write($"{t.Rows[i][j]}\t"); } Console.WriteLine(); } }
运行结果：
1 1 zs 20 ls 18 ww 19 1 1 zs 20 ls 18 ww 19 t1 21 t2 22
Access：
测试数据库d1.accdb(mdb亲测可用)，表t1：
数据：
ID xm nl 1 zs 20 2 ls 18 3 ww 19
帮助类OleHelper.cs：
1 using System.Data; 2 using System.Data.OleDb; 3 4 namespace WanJinLiuSoft.DBHelper 5 { 6 internal partial class AccessHelper 7 { 8 OleDbConnection connection; 9 OleDbDataAdapter adapter; 10 OleDbCommand command; 11 DataSet ds; 12 public AccessHelper(string dbpath)// @"\data\example1.mdb" 13 { 14 string s1, s2; 15 16 s1 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="; 17 18 s2 = Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location) + dbpath; 19 20 try 21 { 22 connection = new OleDbConnection(s1 + s2); 23 adapter = new OleDbDataAdapter(); 24 adapter.SelectCommand = new OleDbCommand(); 25 adapter.SelectCommand.Connection = connection; 26 command = new OleDbCommand(); 27 command.Connection = connection; 28 ds = new DataSet(); 29 } 30 catch (Exception e1) 31 { 32 throw new Exception(e1.Message + "仅限Windows系统使用？"); 33 } 34 } 35 public DataTable 读(string sql) 36 { 37 ds.Clear(); 38 adapter.SelectCommand.CommandText = sql; 39 return AdapterRead(); 40 } 41 public DataTable 读_参数化(FormattableString sql) 42 { 43 ds.Clear(); 44 set_Command(sql); 45 adapter.SelectCommand = command; 46 return AdapterRead(); 47 } 48 public string 写(string sql) 49 { 50 command.CommandText = sql; 51 return CommandExecute(); 52 53 } 54 55 public string 写_参数化(FormattableString sql) 56 { 57 set_Command(sql); 58 return CommandExecute(); 59 } 60 } 61 }
帮助辅助类OleHelper_Function.cs：
1 using System.Data; 2 3 namespace WanJinLiusoft.DBHelper 4 { 5 internal partial class AccessHelper 6 { 7 void set_Command(FormattableString x) 8 { 9 var t = x.Format; 10 var args = x.GetArguments(); 11 command.Parameters.Clear(); 12 for (int i = 0; i < args.Length; i++) 13 { 14 t = t.Replace("{" + i + "}", $"@p{i}"); 15 command.Parameters.AddWithValue($"p{i}", args[i]); 16 } 17 command.CommandText = t; 18 } 19 DataTable AdapterRead() 20 { 21 try 22 { 23 adapter.Fill(ds); 24 return ds.Tables[0]; 25 } 26 catch (Exception e) 27 { 28 DataTable table = new DataTable(); 29 DataColumn column; 30 DataRow row; 31 32 column = new DataColumn(); 33 column.DataType = System.Type.GetType("System.String"); 34 column.ColumnName = "Message"; 35 table.Columns.Add(column); 36 37 row = table.NewRow(); 38 row[0] = e.Message; 39 table.Rows.Add(row); 40 41 return table; 42 } 43 } 44 string CommandExecute() 45 { 46 connection.Open(); 47 try 48 { 49 return command.ExecuteNonQuery().ToString(); 50 } 51 catch (Exception e) 52 { 53 return e.Message; 54 } 55 finally 56 { 57 connection.Close(); 58 } 59 } 60 } 61 }
测试主程序：
using System.Data; namespace ConsoleApp1 { internal class Program { static void Main(string[] args) { var t1 = "t2"; var t2 = 22; var db = new AccessHelper( @"\data\d1.mdb"); Console.WriteLine(db.写("insert into t1(xm,nl) values('t1',21)")); Console.WriteLine(db.写_参数化($"insert into t1(xm,nl) values({t1},{t2})")); var dt = db.读_参数化($"select * from t1"); showTable(dt); Console.WriteLine(db.写("delete from t1 where xm='t1'")); Console.WriteLine(db.写_参数化($"delete from t1 where xm={t1}")); dt = db.读_参数化($"select * from t1"); showTable(dt); } static void showTable(DataTable t) { for (int i = 0; i < t.Rows.Count; i++) { for (int j = 0; j < t.Columns.Count; j++) { Console.Write($"{t.Rows[i][j]}\t"); } Console.WriteLine(); } } } }
运行结果：
1 1 1 zs 20 2 ls 18 3 ww 19 4 t1 21 5 t2 22 1 1 1 zs 20 2 ls 18 3 ww 19
MySql（如前所述，待验证，注意下载的NuGet包是“MySql.Data”）：
操作类MySqlHelper.cs：
using System.Data; using MySql.Data.MySqlClient; namespace WanJinLiuSoft.DBHelper { internal partial class MySqlHelper { MySqlConnection connection; MySqlDataAdapter adapter; MySqlCommand command; DataSet ds; public MySqlHelper(string ip_add, string user_name, string user_pass, string db_name) { string s1 = $"server={ip_add};initial catalog={db_name};user ID={user_name};password={user_pass};"; connection = new MySqlConnection(s1); adapter = new MySqlDataAdapter(); adapter.SelectCommand = new MySqlCommand(); adapter.SelectCommand.Connection = connection; command = new MySqlCommand(); command.Connection = connection; ds = new DataSet(); } public DataTable 读(string sql) { ds.Clear(); adapter.SelectCommand.CommandText = sql; return AdapterRead(); } public DataTable 读_参数化(FormattableString sql) { ds.Clear(); set_Command(sql); adapter.SelectCommand = command; return AdapterRead(); } public string 写(string sql) { command.CommandText = sql; return CommandExecute(); } public string 写_参数化(FormattableString sql) { set_Command(sql); return CommandExecute(); } } }
操作辅助类MySqlHelper_Function.cs：
using System.Data; namespace WanJinLiuSoft.DBHelper { internal partial class MySqlHelper { void set_Command(FormattableString x) { var t = x.Format; var args = x.GetArguments(); command.Parameters.Clear(); for (int i = 0; i < args.Length; i++) { t = t.Replace("{" + i + "}", $"?p{i}"); command.Parameters.AddWithValue($"p{i}", args[i]); } command.CommandText = t; } DataTable AdapterRead() { try { adapter.Fill(ds); return ds.Tables[0]; } catch (Exception e) { DataTable table = new DataTable(); DataColumn column; DataRow row; column = new DataColumn(); column.DataType = System.Type.GetType("System.String"); column.ColumnName = "Message"; table.Columns.Add(column); row = table.NewRow(); row[0] = e.Message; table.Rows.Add(row); return table; } } string CommandExecute() { connection.Open(); try { return command.ExecuteNonQuery().ToString(); } catch (Exception e) { return e.Message; } finally { connection.Close(); } } } }