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();
            }
        }

运行结果：

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();
            }
        }
    }
}

相关阅读:
选择排序
 转：ASP.NET MVC中Unobtrusive Ajax的妙用
 转：MVC 下导航超链接本页面高亮的一种解决方案
 转载：iOS 推送的服务端实现
 转载：Unobtrusive JavaScript in ASP.NET MVC 3 隐式的脚本在MVC3
待实践三：MVC3下路由的测试使用 RouteDebug.dll 来测试判断路由是否符合
 待实践二：MVC3下的3种验证 (1)前台 jquery validate验证 (2)MVC实体验证 (3)EF生成的/自己手写的自定义实体校验(伙伴类+元素据共享)
待实践一：仿造博客园后台上传头像并切图生成缩略图fine uploader.js jcrop.js
Jquery 模板插件 jquery.tmpl.js 的使用方法(1):基本语法，绑定,each循环，ajax获取json数据
 Linq 时间对比陷阱坑
原文地址：https://www.cnblogs.com/wanjinliu/p/16407651.html