使用C# 进行相对安全的数据库访问

使用C# 进行相对安全的数据库访问

基本的数据库访问

先看看基本的数据库访问，直接上代码，都已经做了注解

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace ReportOrders
{
    class Peport
    {
        
        static void Main(string[] args)
        {
            SqlConnection dataConnection = new SqlConnection();//新建一个数据库的连接
            try
            {
                SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();//构建一个连接SQL的字符串
                builder.DataSource = ".\\SQLExpress";//sqlServer实例名称
                builder.InitialCatalog = "Northwind";//使用的数据库名称
                builder.IntegratedSecurity = true;//使用windows验证
                /*
                 * 使用账号密码验证，也可自行百度 
                 * 不要在程序中硬编码用户名和密码，任何人只要获取了源代码的一个副本（或者对已编译的代码进行反向工程），
                 * 就可以看到这个信息，从而使整个安全机制沦为笑话
                 */
                 /*
                  * SqlConnection myConnection = new SqlConnection();
                  * string userName="...";
                  * string password="...";
                  * string connString = $"User ID={userName};password={password};Initial Catalog=Northwind;Data Source=你的计算机\\SQLExpress";
                  * myConnection.ConnectionString = connString;
                 */
                dataConnection.ConnectionString = builder.ConnectionString;//把构建的字符串给数据库连接
                dataConnection.Open();
                Console.Write("请输入一个客户ID（5个字符）：");
                string customerId = Console.ReadLine();//读取一个字符串

                SqlCommand dataCommand = new SqlCommand();//新建一个Sql命令
                dataCommand.Connection = dataConnection;
                dataCommand.CommandType = CommandType.Text;//命令类型是文本类型
                dataCommand.CommandText = $"select OrderID,OrderDate,ShippedDate,ShipName,ShipAddress,ShipCity,ShipCountry from Orders where CustomerID=@CustomerIdParam";
                /*
                 * @CustomerIdParam是一个SQL参数的点位符（@符号向数据提供程序指明这是一个参数，而不是数据库中一列）
                 * 这样是为了避免SQL注入攻击
                 */
                //创建一个Sql参数，可以在执行SqlCommand对象时替换@声明的变量，变量被声明成数据库Char类型（定长字符串的SQL Server等价物）,最长5个字符串
                SqlParameter param = new SqlParameter("@CustomerIdParam", SqlDbType.Char, 5);
                param.Value = customerId;
                dataCommand.Parameters.Add(param);
                //dataCommand.Parameters.Add("@CustomerIdParam", SqlDbType.VarChar, 80).Value = customerId;

                Console.WriteLine($"About to find orders for customer {customerId}");
                SqlDataReader dataReader = dataCommand.ExecuteReader();
                //SqlDataReader执行SQL命令，得到返回数据,而且每次只获取一行，并且获取一行后不在这行保留任何锁，
                //避免了锁定数据库的某行，从而使其它进程无法读取
                while (dataReader.Read())
                {
                    
                    int orderId = dataReader.GetInt32(0);
                    if (dataReader.IsDBNull(2))//判断一下第二列数据是否为空，使用get读取空数据会抛出异常
                    {
                        Console.WriteLine($"Order {orderId} not yet shiped\n\n");
                    }
                    else
                    {
                        DateTime orderDate = dataReader.GetDateTime(1);
                        DateTime dateTime = dataReader.GetDateTime(2);
                        string shipName = dataReader.GetString(3);
                        string shipAddress = dataReader.GetString(4);
                        string shipCity = dataReader.GetString(5);
                        string shipCountry = dataReader.GetString(6);
                        Console.WriteLine($"Order:{orderId}\nPlaced:{orderDate}\nshipped:{dateTime}\nTo Address:{shipName}\n{shipAddress}\n{shipCity}\n{shipCountry}\n\n");
                    }
                    
                }
                dataReader.Close();
            }
            catch (SqlException e)
            {
                Console.WriteLine($"Error accessing the database:{e.Message}");
            }
            finally
            {
                dataConnection.Close();
            }
        }
    }
}

使用LINQ to SQL查询数据库

LINQ to SQL提供了一个高级的抽象，使你不再需要操心具体如何构建ADO.NET Command对象，不再需要操心如何遍历由DataReader对象返回的结果集，也不再需要操心如何使用各种GetXXX方法来逐列的获取数据

我们需要先定义一个实体类，对应数据库中的一张表，与新建一张相似的表类似
在这个类中针对表中自己感兴趣的每一列，都建一个属性（并不需要包含所有的列），但是没有建相应属性的列肯定是获取不到数据的。

 	[Table(Name = "Products")] //name是表名，如果省略默认是类名
    class Product
    {
        [Column(IsPrimaryKey = true, CanBeNull = false)]//定义它是主键，不能为空，其实也有name属性，不指定默认数据库列名和属性名称一致
        public int ProductID { get; set; }

        [Column(CanBeNull = false)]
        public string ProductName { get; set; }

        [Column]
        public int? SupplierID { get; set; }

        [Column(DbType = "money")]//因为C#中没有money类型，所以要映射成decimal（虽然默认自己就会把money映射成decimal，但是为了语法展示，所以写出来了）
        public decimal? UnitPrice { get; set; }
    }

然后就简单了

using System;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Data.SqlClient;

namespace LINQ
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
            builder.DataSource = ".\\SQLExpress";
            builder.InitialCatalog = "Northwind";
            builder.IntegratedSecurity = true;

            DataContext db = new DataContext(builder.ConnectionString);

            Table<Product> products = db.GetTable<Product>();
            //实时数据
            //var productsQuery = from p in products select p;
            //foreach (var product in productsQuery)
            //{
            //    Console.WriteLine($"ID:{product.ProductID},Name:{product.ProductName},Supplier:{product.SupplierID},Price{product.UnitPrice:C}");
            //}
            //历史数据
            //var productsQuery = from p in products.ToList() select p;
            //foreach (var product in productsQuery)
            //{
            //    Console.WriteLine($"ID:{product.ProductID},Name:{product.ProductName},Supplier:{product.SupplierID},Price{product.UnitPrice:C}");
            //}
            foreach (var product in products)
            {
                Console.WriteLine($"ID:{product.ProductID},Name:{product.ProductName},Supplier:{product.SupplierID},Price{product.UnitPrice:C}");
            }

        }
    }

    [Table(Name = "Products")]
    class Product
    {
        [Column(IsPrimaryKey = true, CanBeNull = false)]
        public int ProductID { get; set; }

        [Column(CanBeNull = false)]
        public string ProductName { get; set; }

        [Column]
        public int? SupplierID { get; set; }

        [Column(DbType = "money")]
        public decimal? UnitPrice { get; set; }
    }
}

是不是比上面简单多了！
而实际上