C#数据库操作

一、连接数据库

1、安装

需要安装SQL Server 与 SQL Server Management Studio

2、连接

在VS项目中的App.config中添加代码：

  <connectionStrings>
    <add name ="dbConnStr" connectionString="Data Source=.; Initial Catalog=test; User ID=sa; Password=123456"/>
  </connectionStrings>

要使用是需要引用System.Configuration，读取代码：

private static string connStr = ConfigurationManager.ConnectionStrings["dbConnStr"].ConnectionString;

二、SQL常用语句

创建：create table Student(Id identity(1,1) primary key, Name nvarchar(30) not null, Age nvarchar(10) not null, Fav nvarchar(50));

-- 自动增长不能由我们控制，但是如果设定会话级参数identity_insert就可以了
SET IDENTITY_INSERT AreaFull ON;

插入：insert into table(Name, Age )  value ('Sue', 20 )

查询：select * from table where name=‘Sue’

更新：update table Set Age=Age+1, fav='编程'  where Name='Sue'

删除：delete from Table where Age=20 and Fav is NULL

三、SqlHelper

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

namespace 连接数据库
{
    class SqlHelper
    {
        private static string connStr = ConfigurationManager.ConnectionStrings["dbConnStr"].ConnectionString;

        public static int ExecuteNoQuery(string sqlStr, params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sqlStr;
                    cmd.Parameters.AddRange(parameters);
                    return cmd.ExecuteNonQuery();
                }
            }
        }

        public static Object ExecuteScalar(string sqlStr, params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sqlStr;
                    cmd.Parameters.AddRange(parameters);
                    return cmd.ExecuteScalar();
                }
            }
        }

        public static SqlDataReader ExecuteReader(string sqlStr, params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sqlStr;
                    cmd.Parameters.AddRange(parameters);
                    SqlDataReader data = cmd.ExecuteReader();
                    return cmd.ExecuteReader();
                }
            }
        }

        public static DataTable ExecuteDataTable(string sqlStr, params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sqlStr;
                    cmd.Parameters.AddRange(parameters);
                    DataSet dataSet = new DataSet();
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    adapter.Fill(dataSet);
                    return dataSet.Tables[0];
                }
            }
        }
    }
}

四、批量添加

DataTable table = new DataTable();   
            table.Columns.Add("studName");
            table.Columns.Add("studAge");
            table.Columns.Add("studFav");

            FileStream fileStream = new FileStream(path, FileMode.Open, FileAccess.Read);
            StreamReader reader = new StreamReader(fileStream, Encoding.Default);
            string line = reader.ReadLine();
            while (line != null)
            {
                string[] segs = line.Split('\t');
                string name = segs[0];
                string age = segs[1];
                string fav = segs[2];

                DataRow row = table.NewRow();
                row["studName"] = name;
                row["studAge"] = age;
                row["studAge"] = fav;
                table.Rows.Add(row);

                line = reader.ReadLine();
            }

            //批量插入语句
            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(
              ConfigurationManager.ConnectionStrings["dbConnStr"].ConnectionString))
            {
                bulkCopy.DestinationTableName = "T_Student";
                bulkCopy.ColumnMappings.Add("studName", "Name");
                bulkCopy.ColumnMappings.Add("studAge", "Age");
                bulkCopy.ColumnMappings.Add("studAge", "Fav");
                bulkCopy.WriteToServer(table);
            }

五、数据库中含NULL数据处理  DBNull

1、读取数据

 DataTable table = SqlHelper.ExecuteDataTable("select * from T_Student where Id=0");
            DataRow row = table.Rows[0];   //结果不可能为空
            string name;
            if (row["Name"] == DBNull.Value)
            {
                name = null;
            }
            else
            {
                name = (string)row["Name"];
            }
            int? age;
            if (row["Age"] == DBNull.Value)
            {
                age = null;
            }
            else
            {
                age = (int)row["Age"];
            }

2、存入数据

 string name = txtName.Text;
            string age = txtAge.Text;
            object objName;
            if (name.Length <= 0)
            {
                objName = DBNull.Value;
            }
            else
            {
                objName = name;
            }
            object objAge;
            if (age.Length <= 0)
            {
                objAge = DBNull.Value;
            }
            else
            {
                objAge = age;
            }
            SqlHelper.ExecuteNoQuery(@"Insert into T_Student(Name,Age)
                values(@Name, @Age)", 
                new SqlParameter("@Name", objName),
                new SqlParameter("@Age", objAge));

六、主键  Guid

类型 ：uniqueidentifier

生成：网卡MAC、地址、纳秒级时间 、芯片ID码等计算

SQL Server中生成GUID的函数newid()；.net中生成Guid的方法Guid.NewGuid()。

七、备注

1、获取自增字段的值

//获得自增字段的值
                    cmd.CommandText = "insert into T_Student(Name,Age) values('aaa',123) ;select @@identity";
                   // cmd.CommandText = "insert into T_Student(Name,Age) output inserted.Id values('aaa',123) ";
                    long i = (long)cmd.ExecuteScalar();//bigint→long