SQL SERVER 笔记

1、当 Sql Server 2008 R2 中的数据库日志文件比较大时，可以用以下脚本来清理日志

ALTER DATABASE test_log4net SET RECOVERY SIMPLE WITH NO_WAIT;
ALTER DATABASE test_log4net SET RECOVERY SIMPLE;
DBCC SHRINKFILE (N'test_log4net_log' , 1, TRUNCATEONLY);  --第二个参数:0=行、1=日志、2=FILESTREAM
ALTER DATABASE test_log4net SET RECOVERY FULL WITH NO_WAIT;
ALTER DATABASE test_log4net SET RECOVERY FULL;

2、ADO.NET 事务用法

private static void ExecuteSqlTransaction(string connectionString)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();

        SqlCommand command = connection.CreateCommand();
        SqlTransaction transaction;

        // 启动一个本地事务
        transaction = connection.BeginTransaction("SampleTransaction");

        // 必须为挂起的事务指定事务对象和 Command 对象的连接对象
        command.Connection = connection;
        command.Transaction = transaction;

        try
        {
            command.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')";
            command.ExecuteNonQuery();
            command.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')";
            command.ExecuteNonQuery();

            // 尝试提交事务
            transaction.Commit();
            Console.WriteLine("Both records are written to database.");
        }
        catch (Exception ex)
        {
            Console.WriteLine("Commit Exception Type: {0}", ex.GetType());
            Console.WriteLine("  Message: {0}", ex.Message);

            // 尝试回滚事务
            try
            {
                transaction.Rollback();
            }
            catch (Exception ex2)
            {
                // 此异常捕获代码将处理服务器端可能出现的任何错误，出错后会导致回滚失败，例如：连接已关闭
                Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
                Console.WriteLine("  Message: {0}", ex2.Message);
            }
        }
    }
}

 3、.NET 4.0 项目中引用 .NET 2.0 版的程序集

<?xml version="1.0"?>
<configuration>
  <startup useLegacyV2RuntimeActivationPolicy="true">
    <supportedRuntime version="v4.0" sku = ".NETFramework,Version=v4.0"/>
    <supportedRuntime version="v2.0.50727"/>
  </startup>
</configuration>

4、SQL SERVER 分页查询语句：（注意：row_number()、rank()、dense_rank() 的区别）

SELECT * FROM
( 
    SELECT ROW_NUMBER() OVER (ORDER BY LocationID) as RowNo
           ,LocationCode
           ,LocationName
      FROM [CMSDB].[dbo].[Location]
) AS T
WHERE RowNo > 2 and RowNo < 4

 5、查询照片表里最新的照片记录

SELECT P1.ID, P1.PHOTO_CONTENT, P1.EMP_NO, P1.MODIFY_TIME, P1.DELETE_FLAG, P1.RECORD_COUNTER FROM HT_PHOTO P1
WHERE P1.RECORD_COUNTER > 0 AND P1.IS_QUALIFIED = 1 
AND EXISTS(SELECT MID FROM (SELECT MAX(ID) AS MID FROM HT_PHOTO GROUP BY EMP_NO) P2 WHERE P1.ID = P2.MID) --执行效率高一些
--AND ID IN (SELECT MAX(ID) FROM HT_PHOTO P2 GROUP BY P2.EMP_NO)
--JOIN (SELECT MAX(ID) FROM HT_PHOTO GROUP BY EMP_NO) P3 ON P1.ID = P3.ID

参考资源：

https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqltransaction.rollback?redirectedfrom=MSDN&view=netframework-4.7.2#System_Data_SqlClient_SqlTransaction_Rollback

附：CRUD 操作的 EF/ADO.NET 实现

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

namespace EFDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            // EF(需要先添加.edmx文件)
            HT_ACCESSEntities db = new HT_ACCESSEntities();
            Console.WriteLine("卡片数量：{0}
", db.HT_CARD.Count());

            IEnumerable<HT_CARD> rows = db.HT_CARD.Select(x => x);
            foreach (HT_CARD p in rows)
            {
                int id = p.ID;
                string empNo = p.EMP_NO;
                DateTime? time = p.CREATE_TIME;
                Console.WriteLine("卡信息：{2}	{0}	{1}", id, empNo, time.ToString());
            }

            // ADO.NET
            string connectionString = @"data source=.;initial catalog=HT_ACCESS;user id=sa;password=;
         connect timeout=30;
         MultipleActiveResultSets=True;
         App=EntityFramework";
            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand("SELECT * FROM HT_CARD", conn);
            conn.Open();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dataTable = new DataTable();
            da.Fill(dataTable);
            conn.Close();
            da.Dispose();
            Console.WriteLine("卡片数量：{0}
", dataTable.Rows.Count);
            
            foreach(DataRow row in dataTable.Rows)
            {
                int id = int.Parse(row["ID"].ToString());
                string empNo = row["EMP_NO"].ToString();
                DateTime time = DateTime.Parse(row["CREATE_TIME"].ToString());
                Console.WriteLine("卡信息：{2}	{0}	{1}", id, empNo, time.ToString());
            }

            Console.ReadKey();
        }
    }
}