标准库

标准库里使用 System.Data.SqlClient命名空间

普通的增、删、改、查

private static readonly string sqlconnection =
            "Data Source=JG-ZHANGMENG2;Initial Catalog=Quotation;Integrated Security=true;Timeout=360";

using (var conneciton = new SqlConnection(sqlconnection))
{
    string query = @"Select Name, ClosePrice from Test";
    SqlCommand cmd = new SqlCommand(query, connection);
    connection.open();
    //这是查询的写法，若是增、删、改写成cmd.ExecuteNonQuery()
    SqlDataReader reader = cmd.ExecuteReader();
    while(reader.read())
    {
        //GetDouble(0),是说从第一列取出一个double类型的变量，其他依次类推
        Console.WriteLine(reader.GetDouble(0));
    }
}

调用存储过程

private static readonly string sqlconnection =
            "Data Source=JG-ZHANGMENG2;Initial Catalog=Quotation;Integrated Security=true;Timeout=360";

using (var conneciton = new SqlConnection(sqlconnection))
{
    string storageProcedure = "DBO.GET_STOCK"
    SqlCommand cmd = new SqlCommand(stroageProcedure, connection);
    cmd.CommandType = CommandType.StoredProcedure;
    //添加存储过程参数
    cmd.Parameters.Add(new SqlParameter("@StockCode", stockCode);
    connection.open();
    //若存储过无返回，则写成cmd.ExecuteNonQuery()
    SqlDataReader reader = cmd.ExecuteReader();
    while(reader.Read())
    {
        Console.WriteLine(reader.GetDouble(0));
    }
}

LINQ

LINQ提供 对多种数据源的统一查询语法，常用的数据源是SQL、对象列表、XML等

LINQ一共有两种语法类型

1. Lambda语法

   var longWords = words.Where( w => w.length > 10);
   

2. Query语法

   var longwords = from w in words where w.length > 10;
   

常用方法

//WHERE 过滤
List<string> words = new List<string>() { "abc", "bcd","cdef", "fedad" };

var query =
    from word in words
    where word.Length > 3
    select word;
;

foreach (string s in query)
    Console.WriteLine(s); //return "cdef", "fedad“

//SELECT 投影操作
List<string> words = new List<string>() { "an", "apple", "a", "day" };

var query = from word in words
            select word.Substring(0, 1);

foreach (string s in query)
     Console.WriteLine(s); //return "a" "a" "a" "a"


//SELECTMANY 多重投影
//selectMany相当于简化二重循环的简化，外层查询的结果供内层使用
List<string> phrases = new List<string>() { "an apple a day", "the quick brown fox" };

var query = from phrase in phrases
            from word in phrase.Split(' ')
            select word;

foreach (string s in query)
  Console.WriteLine(s); //"an" "apple" "a" "day" "the" "quick" "brown" "fox"

//ORDER BY 排序
int[] nums = { -20, 12, 6, 10, 0, -3, 1 };

var query =
    from n in nums
    orderby n descending 
    select n;

foreach (int s in query)
    Console.WriteLine(s); //return 12 10 6 1 0 -3 -20
//GROUP BY 分组

//JOIN 连接
namespace Operators
{
  class JoinTables
  {
     class DepartmentClass
     {
        public int DepartmentId { get; set; }
        public string Name { get; set; }
     }

     class EmployeeClass
     {
        public int EmployeeId { get; set; }
        public string EmployeeName { get; set; }
        public int DepartmentId { get; set; }
     }

     static void Main(string[] args)
     {
        List <DepartmentClass> departments = new List <DepartmentClass>();
              departments.Add(new DepartmentClass { DepartmentId = 1, Name = "Account" });
              departments.Add(new DepartmentClass { DepartmentId = 2, Name = "Sales" });
              departments.Add(new DepartmentClass { DepartmentId = 3, Name = "Marketing" });

        List <EmployeeClass> employees = new List <EmployeeClass>();
              employees.Add(new EmployeeClass { DepartmentId = 1, EmployeeId = 1, EmployeeName = "William" });
              employees.Add(new EmployeeClass { DepartmentId = 2, EmployeeId = 2, EmployeeName = "Miley" });
              employees.Add(new EmployeeClass { DepartmentId = 1, EmployeeId = 3, EmployeeName = "Benjamin" });


        var list = (from e in employees
                   join d in departments on e.DepartmentId equals d.DepartmentId
                   select new
                   {
                       EmployeeName = e.EmployeeName,
                       DepartmentName = d.Name
                   });

        foreach (var e in list)
        {
           Console.WriteLine("Employee Name = {0} , Department Name = {1}",
                             e.EmployeeName, e.DepartmentName);
        }

        Console.WriteLine("\nPress any key to continue.");
        Console.ReadKey(); 
     }
  }
}

Dapper

Dapper是一个轻量级的ORM库，简单说可以方面的把对象存成数据库的记录或者把数据库的记录取出来变成对象。

数据库的列和对象的属性应一一对应

//插入
Using(var connection = new SqlConnection(sqlconnection))
{
    string query = "INSERT INTO Users values (@UserName, @Email, @Address);
    connection.execute(query,user);
    //Connection.execute(query, new{UserName="jack", Email="[email protected]",Address="abc");
}
//更新
Using(var connection = new SqlConnection(sqlconnection))
{
    string query = "Update Users set UserID = 2 WHERE UserName=@UserName"， new{UserName = 10});
    var result = connection.Execute(query);
}
//删除
Using(var connection = new SqlConnection(sqlconnection))
{
    string query = "DELETE FROM Users WHERE UserName=@UserName"， new{UserName = 10});
    var result = connection.Execute(query);
}
//查询
Using(var connection = new SqlConnection(sqlconnection))
{
    connection.Query<Users>("Select * from Users where UserName=@UserName", new {UserName="Jack"})
}