C# 数据库

C#为不同类型的数据库提供了一致的访问方式,其基本类型的定义在System.Data.Common命名空间。

每个具体的数据库都实现对于的类型,放在对应的命名空间,比如对于MSSQL,在System.Data.SqlClient命名空间。

以下的操作以mssql为例。

连接到数据库

访问数据库的前提是连接的数据库。

连接数据库

连接数据库需要使用到连接字符串,连接字符串包含了数据库的地址、用户名、密码和其他参数。

在数据库结束时,需要释放资源。

public static void Connect()
{
    using (SqlConnection conn = new SqlConnection())
    {
        conn.ConnectionString = "Data Source=.;Initial Catalog=master;User ID=sa;Password=123456";
        conn.Open();
    }
}

超时时间

在连接数据库时,可以通过ConnectionTimeout属性为连接设置超时时间。

连接字符串构建器

使用构建器,可以方便的构建和修改连接字符串。

public static void ConnectStringBuilder()
{
    SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
    //再次设置连接字符串可以修改连接字符串的部分参数。
    //builder.ConnectionString = connectString;
    builder.DataSource = ".";
    builder.InitialCatalog = "master";
    builder.UserID = "sa";
    builder.Password = "123456";
    Console.WriteLine(builder.ConnectionString);
}

执行语句

使用命令向数据库执行语句。

语句分为查询语句和非查询语句。

以下操作以Product表为例。

create table Product
(
    Id int primary key identity,
    Name nvarchar(200) not null
)

执行非查询语句

非查询语句用来执行增删改和数据定义(DDL)。

使用ExecuteNonQuery执行非查询语句,方法的返回结果是语句影响的记录数。

    public static void NonQuery()
    {
        using (SqlConnection conn = new SqlConnection())
        {
            conn.ConnectionString = "Data Source=.;Initial Catalog=master;User ID=sa;Password=123456";
            conn.Open();
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "insert into Product values('new product')";
            int n = cmd.ExecuteNonQuery();
        }
    }

执行查询语句

查询语句用来获取数据。

使用ExecuteReader执行查询语句,返回结果是一个数据访问器。

数据访问器使用只进的方式访问每一条记录,直至结束。

在数据访问器结束时,需要释放资源。


public static void Query()
{
    using (SqlConnection conn = new SqlConnection())
    {
        conn.ConnectionString = "Data Source=.;Initial Catalog=master;User ID=sa;Password=123456";
        conn.Open();
        SqlCommand cmd = conn.CreateCommand();
        cmd.CommandText = "select Id,Name from Product";
        using (SqlDataReader reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                int id = reader.GetInt32(0);
                string name = reader.GetString(1);
                Console.WriteLine($"Id={id},Name={name}");
            }
        }
    }
}

多结果查询

查询语句可以包含多个结果(表),通过调用NextResult可以获得下一条结果。

     public static void QueryMulti()
        {
            using (SqlConnection conn = new SqlConnection())
            {
                conn.ConnectionString = "Data Source=.;Initial Catalog=master;User ID=sa;Password=123456";
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "select Id,Name from Product;select Name from sys.databases";

                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    do
                    {
                        //打印表头
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            string name = reader.GetName(i);
                            Console.Write($"{name}");
                            Console.Write("\t");
                        }
                        Console.WriteLine();

                        while (reader.Read())
                        {
                            //打印行
                            for (int i = 0; i < reader.FieldCount; i++)
                            {
                                object value = reader.GetValue(i);
                                Console.Write($"{value}");
                                Console.Write("\t");
                            }
                            Console.WriteLine();
                        }

                        Console.WriteLine("---------------");

                    } while (reader.NextResult());
                }
            }
        }

在使用NextResult需要配合do while循环,后判断是否还有结果。

一般很少使用多结果查询,因为不同结果的列名不一致,不好处理。

一般只在做通用查询工具时使用。

获取单一值

通过调用ExecuteScalar方法,可以获取结果的第一行第一列,特别适合获取像求和这样的单一结果。

语句超时时间

在执行语句时,可以通过CommandTimeout属性设置语句的超时时间。

参数化查询

使用拼接的方式连接字符串,可能造成sql注入。

    public static void NonQueryConcat()
    {
        //假设该参数来自用户查询
        string name = "new product";
        using (SqlConnection conn = new SqlConnection())
        {
            conn.ConnectionString = "Data Source=.;Initial Catalog=master;User ID=sa;Password=123456";
            conn.Open();
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "insert into Product values('"+ name + "')";
            int n = cmd.ExecuteNonQuery();
        }
    }

如果上面的name参数是new product');truncate table Product;--,那么表就会被删除。

根本原因是,拼接字符串改变了sql的语义。

参数化查询可以在查询语句中添加参数占位符,并在命令中设置对应的参数。

如果要为参数化查询的值提供null,需要使用DbNull.Value

public static void NonQueryParam()
{
    //假设该参数来自用户查询
    string name = "new product";
    using (SqlConnection conn = new SqlConnection())
    {
        conn.ConnectionString = "Data Source=.;Initial Catalog=master;User ID=sa;Password=123456";
        conn.Open();
        SqlCommand cmd = conn.CreateCommand();
        cmd.CommandText = "insert into Product values(@Name)";
        cmd.Parameters.Add(new SqlParameter
        {
            ParameterName = "Name",
            Value = name
        });
        int n = cmd.ExecuteNonQuery();
        Console.WriteLine(n);
    }
}

以上在sql语句中,使用了@Name作为占位符,并向SqlCommandParameters添加了新参数。

如果为name提供上面一样的特殊数据,不会造成sql注入,只会添加一条记录。根本原因是,参数化查询确定了sql的语义,不会被修改。

在为参数设置值的时候,默认会推测类型。在某些时候,可能导致和数据库实际类型不一致,比如Unicode字符串和非Unicode字符串,使查询变得低效。这时需要手动设置参数的类型和长度。

不同的数据库对参数化查询有不同的支持,某些数据库不支持命名参数,使用?作为占位符,这个时候,参数的添加顺序必须严格一致;某些数据库支持命名参数,但可能使用不同的前缀,命名参数需要在添加参数时设置名称。

输出参数

可以将参数值作为输出使用。

public static void NonQueryParamOut()
{
    using (SqlConnection conn = new SqlConnection())
    {
        conn.ConnectionString = "Data Source=.;Initial Catalog=master;User ID=sa;Password=123456";
        conn.Open();
        SqlCommand cmd = conn.CreateCommand();
        cmd.CommandText = "select @Version = @@version";
        cmd.Parameters.Add(new SqlParameter
        {
            ParameterName = "Version",
            SqlDbType = SqlDbType.NVarChar,
            Size = 1000,
            Direction = ParameterDirection.Output
        });
        int n = cmd.ExecuteNonQuery();
        Console.WriteLine(n);
        Console.WriteLine(cmd.Parameters["Version"].Value);
    }
}

在使用输出参数时,需要设置参数的类型和方向。

使用输出参数,可以在简单查询时使用非查询语句,将查询结果以输出参数的方式传递。

执行存储过程

存储过程是数据库中已编写的语句。

通过设置命令的类型,可以执行存储过程。

存储过程可以是查询语句和非查询语句,并且支持返回值和输出参数。

      public static void Proc()
        {
            using (SqlConnection conn = new SqlConnection())
            {
                conn.ConnectionString = "Data Source=.;Initial Catalog=master;User ID=sa;Password=123456";
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "GetProduct()";
                cmd.ExecuteNonQuery();
            }
        }

已准备版本

如果一个语句进行被执行,可以通过调用Prepare创建一个语句的已准备版本并缓存,后续执行可以提高效率。

事务

事务用来保证数据的一致性。多个语句要么都成功,要么都不成功。

使用事务

public static void Trans()
{
    using (SqlConnection conn = new SqlConnection())
    {
        conn.ConnectionString = "Data Source=.;Initial Catalog=master;User ID=sa;Password=123456";
        conn.Open();

        using (SqlTransaction trans = conn.BeginTransaction())
        {
            try
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.Transaction = trans;
                cmd.CommandText = "insert into Product values('product1')";

                int n = cmd.ExecuteNonQuery();

                trans.Commit();
            }
            catch (Exception)
            {
                trans.Rollback();
                throw;
            }
        }

    }
}

以上代码展示了事务的一般使用。通过连接开始事务,为每个需要一起执行的命令管理事务,在成功是提交事务,在异常时回滚事务。

一般,事务用于多条语句,单条语句数据库会自己保证一致性。

隔离级别

隔离级别决定了事务的隔离程度,在开始事务时,可以设置隔离级别。

IsolationLevel.ReadUncommitted允许脏读。可以读取未提交事务的数据。

IsolationLevel.ReadCommitted读已提交。可能导致不可重复读和幻想读。

IsolationLevel.RepeatableRead可重复读。可能导致幻象读。

IsolationLevel.Serializable可序列化。阻止其他用户更新或添加、删除表的记录。

保存点

mssql的事务支持保存点,支持保存和回滚事务的一部分。

数据库提供器工厂

数据库提供器工厂提供了一系列工厂方法,创建相关类型。

  public static void DbProvider()
        {
            //通过数据库提供器名称获取提供器工厂。提供器名称需要在配置文件设置。
            DbProviderFactory providerFactory = DbProviderFactories.GetFactory("System.Data.SqlClient");

            using (DbConnection conn = providerFactory.CreateConnection())
            {
                conn.ConnectionString = "Data Source=.;Initial Catalog=master;User ID=sa;Password=123456";
                conn.Open();
                DbCommand cmd = conn.CreateCommand();
                cmd.CommandText = "insert into Product values('product1')";
                int n = cmd.ExecuteNonQuery();
            }
        }

使用数据库提供器工厂,可以使用一致的方法访问不同的数据库。缺点是不能使用数据库特有的一些功能。

内存数据库

C#提供了相关类型,用来使用内存保存数据库结构和数据。

DataSet,DataTable,DataRow,DataColumn分别对应数据库,数据表,数据行,数据列。

内存数据库的使用

public static void DataTableUse()
{
    DataTable dt = new DataTable();

    //添加列
    dt.Columns.Add("Id", typeof(int));
    dt.Columns.Add("Name", typeof(string));

    //创建和列一致的行
    var row = dt.NewRow();

    //设置行的数据
    row["Id"] = 1;
    row["Name"] = "test";

    //添加行
    dt.Rows.Add(row);

    //访问所有行
    foreach (DataRow dataRow in dt.Rows)
    {
        Console.WriteLine("Id={0},Name={1}", row["Id"], row["Name"]);
    }
}

DataSet是多个DataTable的集合相当于一个内存数据库。

行的状态和版本

DataTable可以跟踪行的状态和版本,并支持提交和回滚更改。

行状态

Detached,表示行刚被创建,未添加到表。 Added,表示已添加。 Unchanged,表示未更改。 Deleted,表示行已删除。 Modified, 表示行已修改。

通过DataRow的RowState属性,可以获取行的状态。

行版本

Original,原始值。 Current,当前值。 Proposed,建议值 Default,默认值,对于Detached是建议值,其他是当前值。

通过DataRow的带行版本参数的索引器,可以获取不同版本的值。

提交和回滚表

AcceptChanges可以提交更改,将AddedModified的行更改为Unchanged,行版本使用当前值,将Deleted行删除。

RejectChanges可以回滚更改,将Added行删除,将Modified,Deleted的行更改为Unchanged,行版本使用原始值。

将数据库数据放入内存数据库

使用数据库数据适配器,可以方便的将数据库查询结果放到内存数据库中。

public static void DataTable()
{
    SqlConnection conn = new SqlConnection();
    conn.ConnectionString = "Data Source=.;Initial Catalog=master;User ID=sa;Password=123456";

    SqlCommand cmd = conn.CreateCommand();
    cmd.CommandText = "select Id,Name from Product";

    SqlDataAdapter adapter = new SqlDataAdapter();
    adapter.SelectCommand = cmd;

    DataTable dt = new DataTable();
    adapter.Fill(dt);

    foreach (DataRow row in dt.Rows)
    {
        Console.WriteLine("Id={0},Name={1}", row["Id"], row["Name"]);
    }
}

在使用数据库适配器时,无需手动打开和关闭连接。

使用内存数据库,可以快捷的将数据读取出来,后续可以随时访问。缺点是更占用内存。

DataTable也可以方便的将数据显示到界面,winform,webform都提供了相应的控件来绑定该类型数据。

支持增删改

通过数据库命令构建器,可以根据查询语句生成对应的增删改语句。

通过数据库适配器的Update方法,可以将更改保存到数据库。

public static void DataTableUpdate()
{
    SqlConnection conn = new SqlConnection();
    conn.ConnectionString = "Data Source=.;Initial Catalog=master;User ID=sa;Password=123456";

    SqlCommand cmd = conn.CreateCommand();
    cmd.CommandText = "select Id,Name from Product";

    SqlDataAdapter adapter = new SqlDataAdapter();
    adapter.SelectCommand = cmd;

    SqlCommandBuilder builder = new SqlCommandBuilder(adapter);

    DataTable dt = new DataTable();
    adapter.Fill(dt);

    foreach (DataRow row in dt.Rows)
    {
        Console.WriteLine("Id={0},Name={1}", row["Id"], row["Name"]);
    }

    //添加新行
    var newRow = dt.NewRow();
    newRow["Name"] = "new row";
    dt.Rows.Add(newRow);

    //根据DataTable行状态和值版本,执行对应的sql
    int n = adapter.Update(dt);
    Console.WriteLine(n);
}

通过适配器和命令构建器,可以快捷的对一个表完成所有操作。

缺点是无法精细的控制语句,并且要求查询语句必须有主键。

数据库命令构建器适合配合winform中DataGridView控件使用。

一般很少使用Fill进行更新。