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

Sqlite:

vs2022 v17.9.6 .net8下调试通过。

注意点：1、需要下载的nuget包为：Microsoft.Data.Sqlite，别下错了。2、Sqlite数据库的c#项目属性最好选为“始终复制”。3、连接字符串只支持绝对路径写法；要用相对路径，需要取程序运行位置再拼接。4、它的读写与其他数据库有一点点不一样。

数据库内容：d1，t1，……同前。

SqliteHelper.cs

 1 internal partial class SqliteHelper
 2 {
 3 
 4     SqliteConnection connection;
 5     SqliteCommand command;
 6     public SqliteHelper(string db_name)
 7     {
 8         string s1 = $"Data Source={ db_name}";
 9         connection = new SqliteConnection(s1);
10         command = connection.CreateCommand();
11     }
12     public (SqliteDataReader?, string) 读(string sql)
13     {
14         command.CommandText = sql;
15         return AdapterRead();
16     }
17     public (SqliteDataReader?, string) 读_参数化(FormattableString sql)
18     {
19         set_Command(sql);
20         return AdapterRead();
21     }
22     public string 写(string sql)
23     {
24         command.CommandText = sql;
25         return CommandExecute();
26 
27     }
28 
29     public string 写_参数化(FormattableString sql)
30     {
31         set_Command(sql);
32         return CommandExecute();
33     }
34 }

SqliteHelper_Function.cs：

 1 internal partial class SqliteHelper
 2 {
 3     void set_Command(FormattableString x)
 4     {
 5         var t = x.Format;
 6         var args = x.GetArguments();
 7         command.Parameters.Clear();
 8         for (int i = 0; i < args.Length; i++)
 9         {
10             t = t.Replace("{" + i + "}", $"@p{i}");
11             command.Parameters.AddWithValue($"p{i}", args[i]);
12         }
13         command.CommandText = t;
14     }
15     (SqliteDataReader?,string) AdapterRead()
16     {
17         try
18         {
19             connection.Open();
20             return (command.ExecuteReader(),"OK");
21         }
22         catch(Exception e)
23         {
24             Console.WriteLine(e.ToString());
25             return (null,e.Message);
26         }
27         finally
28         {
29             connection.Close();
30         }
31     }
32     string CommandExecute()
33     {
34         try
35         {
36             connection.Open();
37             return command.ExecuteNonQuery().ToString();
38         }
39         catch (Exception e)
40         {
41             return e.Message;
42         }
43         finally
44         {
45             connection.Close();
46         }
47     }
48 }

主程序：

 1 internal class Program
 2 {
 3     static void Main(string[] args)
 4     {
 5         SqliteHelper sh = new SqliteHelper(Path.Combine(Environment.CurrentDirectory,"d1.db"));
 6         string sql1;
 7         FormattableString sql2;
 8         SqliteDataReader? reader;
 9         int nl;
10         
11         nl = 25;           
12         sql1 = "insert into t1 values('yxl',25)";
13         sql2 = $"delete from t1 where nl={nl}";
14 
15         sql1= sh.写(sql1);
16         Console.WriteLine(sql1);
17         (reader,sql1) = sh.读("select * from t1");
18         if (reader is not null)
19         {
20             show_Reader(reader);
21         }
22         else
23         {
24             Console.WriteLine(sql1);
25         }
26 
27         nl = 15;
28         sql1 = sh.写_参数化(sql2);
29         Console.WriteLine(sql1);
30         (reader,sql1) = sh.读_参数化($"select * from t1 where nl>={nl}");
31         if (reader is not null)
32         {
33             show_Reader(reader);
34         }
35         else
36         {
37             Console.WriteLine(sql1);
38         }
39     }
40     static void show_Reader(SqliteDataReader? sdr)
41     {
42         if (sdr == null)
43         {
44             Console.WriteLine("SqliteDataReader is null!");
45             return;
46         }
47         while (sdr.Read())
48         {
49             for (int i = 0; i < sdr.FieldCount; i++)
50             {
51                 Console.Write(sdr.GetString(i)+"\t");
52             }
53             Console.WriteLine();
54         }
55         sdr.Close();
56     }
57 }

可以用SqliteDataReader对象的HasRows属性，获取一个bool值，该值指示数据读取器是否包含任何行。

运行结果：

posted on 2022-06-24 09:41 万金流阅读(288) 评论(0) 编辑收藏举报