C# ORM学习笔记：Dapper基本用法(2)

当前位置:

首页 > temp > 简明python教程 >

C# ORM学习笔记：Dapper基本用法(2)

; set; } public Manager(ITextTemplatingEngineHost host, StringBuilder template, bool commonHeader) { this.host = host; this.template = template; OutputPath = string.Empty; strategy = ManagementStrategy.Create(host); } public void StartBlock(string name) { currentBlock = new Block { Name = name, Start = template.Length }; } public void StartFooter() { footerBlock.Start = template.Length; } public void EndFooter() { footerBlock.Length = template.Length - footerBlock.Start; } public void StartHeader() { headerBlock.Start = template.Length; } public void EndHeader() { headerBlock.Length = template.Length - headerBlock.Start; } public void EndBlock() { currentBlock.Length = template.Length - currentBlock.Start; blocks.Add(currentBlock); } public void Process(bool split) { string header = template.ToString(headerBlock.Start, headerBlock.Length); string footer = template.ToString(footerBlock.Start, footerBlock.Length); blocks.Reverse(); foreach(Block block in blocks) { string fileName = Path.Combine(OutputPath, block.Name); if (split) { string content = header + template.ToString(block.Start, block.Length) + footer; strategy.CreateFile(fileName, content); template.Remove(block.Start, block.Length); } else { strategy.DeleteFile(fileName); } } } } class ManagementStrategy { internal static ManagementStrategy Create(ITextTemplatingEngineHost host) { return (host is IServiceProvider) ? new VSManagementStrategy(host) : new ManagementStrategy(host); } internal ManagementStrategy(ITextTemplatingEngineHost host) { } internal virtual void CreateFile(string fileName, string content) { File.WriteAllText(fileName, content); } internal virtual void DeleteFile(string fileName) { if (File.Exists(fileName)) File.Delete(fileName); } } class VSManagementStrategy : ManagementStrategy { private EnvDTE.ProjectItem templateProjectItem; internal VSManagementStrategy(ITextTemplatingEngineHost host) : base(host) { IServiceProvider hostServiceProvider = (IServiceProvider)host; if (hostServiceProvider == null) throw new ArgumentNullException("Could not obtain hostServiceProvider"); EnvDTE.DTE dte = (EnvDTE.DTE)hostServiceProvider.GetService(typeof(EnvDTE.DTE)); if (dte == null) throw new ArgumentNullException("Could not obtain DTE from host"); templateProjectItem = dte.Solution.FindProjectItem(host.TemplateFile); } internal override void CreateFile(string fileName, string content) { base.CreateFile(fileName, content); ((EventHandler)delegate { templateProjectItem.ProjectItems.AddFromFile(fileName); }).BeginInvoke(null, null, null, null); } internal override void DeleteFile(string fileName) { ((EventHandler)delegate { FindAndDeleteFile(fileName); }).BeginInvoke(null, null, null, null); } private void FindAndDeleteFile(string fileName) { foreach(EnvDTE.ProjectItem projectItem in templateProjectItem.ProjectItems) { if (projectItem.get_FileNames(0) == fileName) { projectItem.Delete(); return; } } } } #>

DBSchema.ttinclude主要实现了数据库工厂的功能。注：请将数据库连接字符串改成您自己的。

MultiDocument.ttinclude主要实现了多文档的功能。

2.3.2、生成实体类的文本模板

			
<#@ template debug="true" hostspecific="true" language="C#" #>
<#@ assembly name="System.Core" #>
<#@ import namespace="System.Linq" #>
<#@ import namespace="System.Text" #>
<#@ import namespace="System.Collections.Generic" #>
<#@ output extension=".cs" #>
<#@ include file="T4Code/DBSchema.ttinclude"#>
<#@ include file="T4Code/MultiDocument.ttinclude"#>
<# var manager = new Manager(Host, GenerationEnvironment, true) { OutputPath = Path.GetDirectoryName(Host.TemplateFile)}; #>
<#
    //System.Diagnostics.Debugger.Launch();//调试
    var dbSchema = DBSchemaFactory.GetDBSchema();
    List<string> tableList = dbSchema.GetTableList();
    foreach (string tableName in tableList)
    {
        manager.StartBlock(tableName+".cs");
        DataTable table = dbSchema.GetTableMetadata(tableName);

        //获取主键
        string strKey = string.Empty;
        foreach (DataRow dataRow in table.Rows)
        {
            if ((bool)dataRow["ISKEY"] == true)
            {
                strKey = dataRow["FIELD_NAME"].ToString();
                break;
            }
        }
        
#>
//-------------------------------------------------------------------------------
// 此代码由T4模板MultiModelAuto自动生成
// 生成时间 <#= DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") #>
// 对此文件的更改可能会导致不正确的行为，并且如果重新生成代码，这些更改将会丢失。
//-------------------------------------------------------------------------------

using System;
using System.Collections.Generic;
using System.Text;
using Dapper;

namespace LinkTo.Test.ConsoleDapper
{
    [Table("<#= tableName #>")]
    [Serializable]
    public class <#= tableName #>
    {
<#
        foreach (DataRow dataRow in table.Rows)
        {
            //获取数据类型
            string dbDataType = dataRow["DATATYPE"].ToString();
            string dataType = string.Empty;
                    
            switch (dbDataType)
            {
                case "decimal":
                case "numeric":
                case "money":
                case "smallmoney":
                    dataType = "decimal?";
                    break;
                case "char":
                case "nchar":
                case "varchar":
                case "nvarchar":
                case "text":
                case "ntext":
                    dataType = "string";
                    break;
                case "uniqueidentifier":
                    dataType = "Guid?";
                    break;
                case "bit":
                    dataType = "bool?";
                    break;
                case "real":
                    dataType = "Single?";
                    break;
                case "bigint":
                    dataType = "long?";
                    break;
                case "int":
                    dataType = "int?";
                    break;
                case "tinyint":
                case "smallint":
                    dataType = "short?";
                    break;
                case "float":
                    dataType = "float?";
                    break;
                case "date":
                case "datetime":
                case "datetime2":
                case "smalldatetime":
                    dataType = "DateTime?";
                    break;
                case "datetimeoffset ":
                    dataType = "DateTimeOffset?";
                    break;
                case "timeSpan ":
                    dataType = "TimeSpan?";
                    break;
                case "image":
                case "binary":
                case "varbinary":
                    dataType = "byte[]";
                    break;
                default:
                    break;
            }
            if (dataRow["FIELD_NAME"].ToString() == strKey)
            {
#>
        [Key]
        public <#= dataType #> <#= dataRow["FIELD_NAME"].ToString() #> {get; set;}
<#
            }
            else
            {
#>

        public <#= dataType #> <#= dataRow["FIELD_NAME"].ToString() #> {get; set;}
<# 
            }
        }
#>
    }
}
<#
        manager.EndBlock();
    }
    dbSchema.Dispose();
    manager.Process(true);
#>
			

三、CRUD

3.1、connectionStrings

在App.config中添加数据库连接字符串：

			
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <startup> 
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6.1" />
  </startup>
  <connectionStrings>
    <add name="connString" connectionString="Server=.;Database=Test;Uid=sa;Pwd=********;" />
  </connectionStrings>
</configuration>
			

添加一个DapperHelper类，实现数据库连接及后续的CRUD。

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

namespace LinkTo.Test.ConsoleDapper
{
    public class DapperHelper
    {
        public static IDbConnection Connection = null;

        public DapperHelper()
        { }

        private IDbConnection GetCon()
        {
            if (Connection == null)
            {
                Connection = new SqlConnection(ConfigurationManager.ConnectionStrings["connString"].ConnectionString);
            }
            return Connection;
        }
    }
}
			

3.2、Create

a1）通过SQL插入单条数据(带参数)，返回结果是影响行数。

			
        /// <summary>
        /// 通过SQL插入单条数据(带参数)，返回结果是影响行数。
        /// </summary>
        /// <returns></returns>
        public int? InsertWithSqlA()
        {
            using (var conn = GetCon())
            {
                conn.Open();
                string strSql = "INSERT INTO Student (Name,Age,Gender) VALUES (@Name,@Age,@Gender)";
                return conn.Execute(strSql, new { Name = "Hello", Age = 18, Gender = "male" });
            }
        }
			

a2）通过SQL插入单条数据(带实体)，返回结果是影响行数。

			
        /// <summary>
        /// 通过SQL插入单条数据(带实体)，返回结果是影响行数。
        /// </summary>
        /// <returns></returns>
        public int? InsertWithSqlB()
        {
            using (var conn = GetCon())
            {
                conn.Open();
                string strSql = "INSERT INTO Student (Name,Age,Gender) VALUES (@Name,@Age,@Gender)";
                Student student = new Student
                {
                    Name = "Hello",
                    Age = 18,
                    Gender = "male"
                };
                return conn.Execute(strSql, student);
            }
        }
			

a3）通过SQL插入单条数据(带实体)，返回主键值。

			
        /// <summary>
        /// 通过SQL插入单条数据(带实体)，返回主键值。
        /// </summary>
        /// <returns></returns>
        public int? InsertWithSqlC()
        {
            using (var conn = GetCon())
            {
                conn.Open();
                string strSql = "INSERT INTO Student (Name,Age,Gender) VALUES (@Name,@Age,@Gender)";
                Student student = new Student
                {
                    Name = "Hello",
                    Age = 18,
                    Gender = "male"
                };
                strSql += " SELECT SCOPE_IDENTITY()";
                return conn.QueryFirstOrDefault<int>(strSql, student);
            }
        }
			

a4）通过SQL插入多条数据(带实体)，返回结果是影响行数。

			
        /// <summary>
        /// 通过SQL插入多条数据(带实体)，返回结果是影响行数。
        /// </summary>
        /// <returns></returns>
        public int? InsertWithSqlD()
        {
            using (var conn = GetCon())
            {
                conn.Open();
                string strSql = "INSERT INTO Student (Name,Age,Gender) VALUES (@Name,@Age,@Gender)";
                List<Student> list = new List<Student>();
                for (int i = 0; i < 3; i++)
                {
                    Student student = new Student
                    {
                        Name = "World" + i.ToString(),
                        Age = 18,
                        Gender = "male"
                    };
                    list.Add(student);
                }
                return conn.Execute(strSql, list);
            }
        }
			

b）通过实体插入数据，返回结果是主键值。

			
        /// <summary>
        /// 通过实体插入数据，返回结果是主键值。
        /// </summary>
        /// <returns></returns>
        public int? InsertWithEntity()
        {
            using (var conn = GetCon())
            {
                conn.Open();
                var entity = new Student { Name = "World", Age = 18, Gender = "male" };
                return conn.Insert(entity);
            }
        }
			

3.3、Read

a1）通过SQL查询数据（查询所有数据）

		
   共4页: 
上一页
1
2
3
4
下一页

栏目列表