C# SQL封装（三）

2025-01-31 技术教程

本篇接C# SQL（二）

在其代码上继续扩展 删操作 ，让我们开始吧。。。。

一：构建T-SQL语句（在SQL_Structure.cs中添加一个方法 DELETE_T_SQL ，用于构建delete的T-SQL）

usingDBDome.model;usingSystem;usingSystem.Collections.Generic;usingSystem.Linq;usingSystem.Reflection;usingSystem.Text;namespaceDBDome.com{///<summary>///SQL构建器(还没与赋值)///</summary>publicsealedclassSQL_Structure{privateSQL_Structure(){}///<summary>///添加一个数据的T-SQL的构成///</summary>///<typeparamname="T"></typeparam>///<paramname="model"></param>///<returns></returns>publicstaticstringADD_T_SQL<T>(Tmodel)whereT:BaseModel{stringbase_add=@"insertinto[{0}]({1})values({2})";//表名，列明，对应的值Typemodel_type=model.GetType();string[]arr=model_type.Name.Split(newchar[]{'.'});stringtable_name=arr[arr.Length-1];//获得表的名称PropertyInfo[]p_intos=model_type.GetProperties();StringBuildertable_field_name=newStringBuilder();StringBuildertable_field_value=newStringBuilder();PropertyInfoitem=null;for(inti=0;i<p_intos.Length;i++){item=p_intos[i];if(item.Name!="id")//不应添加主键{table_field_name.Append(item.Name+",");table_field_value.Append("@"+item.Name+",");}}returnString.Format(base_add,table_name,table_field_name.ToString().Substring(0,table_field_name.ToString().Length-1),table_field_value.ToString().Substring(0,table_field_value.ToString().Length-1));}///<summary>///删除一条数据的T-SQL构成(以ID号来删除)///</summary>///<typeparamname="T"></typeparam>///<paramname="model"></param>///<returns></returns>publicstaticstringDELETE_T_SQL<T>(Tmodel)whereT:BaseModel{stringbase_delete=@"deletefrom[{0}]where{1}={2}";Typemodel_type=model.GetType();string[]arr=model_type.Name.Split(newchar[]{'.'});stringtable_name=arr[arr.Length-1];//获得表的名称PropertyInfo[]p_intos=model_type.GetProperties();PropertyInfoitem=null;for(inti=0;i<p_intos.Length;i++){item=p_intos[i];if(item.Name=="id"){//返回T-SQL构建returnstring.Format(base_delete,table_name,item.Name,"@"+item.Name);}}returnstring.Empty;}}}

二：构建SqlParameter参数（在SqlParameter_Structure.as中添加方法DELETE_T_SQL）

usingDBDome.model;usingSystem;usingSystem.Collections.Generic;usingSystem.Data.SqlClient;usingSystem.Linq;usingSystem.Reflection;usingSystem.Text;usingSystem.Data;namespaceDBDome.com{///<summary>///SqlParameter///</summary>publicsealedclassSqlParameter_Structure{privateSqlParameter_Structure(){}///<summary>///构建T-SQL参数ADD///</summary>///<typeparamname="T"></typeparam>///<paramname="model"></param>///<returns></returns>publicstaticSqlParameter[]ADD_T_SQL<T>(Tmodel)whereT:BaseModel{Typemodel_type=model.GetType();PropertyInfo[]p_intos=model_type.GetProperties();SqlParameter[]sql_param=newSqlParameter[p_intos.Length-1];//不需要主键PropertyInfoitem=null;stringp_name="";SqlParametercell=null;intj=0;for(inti=0;i<p_intos.Length;i++){item=p_intos[i];p_name=item.Name;if(p_name=="id")continue;//Console.WriteLine("字段{0}的类型为{1}",p_name,item.PropertyType);cell=newSqlParameter(p_name,SqlParameter_Structure.Get_SqlDbType_SqlType(item.PropertyType));cell.Value=item.GetValue(model,null);sql_param[j]=cell;j++;}returnsql_param;}///<summary>///构建T-SQL参数DELETE///</summary>///<typeparamname="T"></typeparam>///<paramname="model"></param>///<returns></returns>publicstaticSqlParameter[]DELETE_T_SQL<T>(Tmodel)whereT:BaseModel{Typemodel_type=model.GetType();PropertyInfo[]p_intos=model_type.GetProperties();SqlParameter[]sql_param=newSqlParameter[1];//只需要主键PropertyInfoitem=null;for(inti=0;i<p_intos.Length;i++){item=p_intos[i];if(item.Name!="id")continue;SqlParametercell=newSqlParameter(item.Name,SqlParameter_Structure.Get_SqlDbType_SqlType(item.PropertyType));cell.Value=item.GetValue(model,null);sql_param[0]=cell;break;}returnsql_param;}privatestaticSqlDbTypeGet_SqlDbType_SqlType(Typecshaper){string[]arr=cshaper.ToString().Split(newchar[]{'.'});stringtoLow=arr[arr.Length-1].ToLower();switch(toLow){case"string":returnSqlDbType.NVarChar;break;case"int16":returnSqlDbType.Bit;break;case"int32":returnSqlDbType.Int;break;default:thrownewException(String.Format("T-SQL参数没有配对的类型{0}",cshaper));break;}}}}

三：测试

先查下数据库，看看能删除的数据：

编写测试代码，开始测试

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DBDome.com;
using DBDome.model;
using System.Data.SqlClient;
using System.Data;

namespace DBDome
{
class Program
{
static void Main(string[] args)
{
#region 添加一条记录
/*
User kayer_new = new User();
kayer_new.name = "kayer";
kayer_new.sex = 1;
kayer_new.lv = 1;
kayer_new.username = "Kayer";
kayer_new.userpwd = "123";

string into_sql = SQL_Structure.ADD_T_SQL<User>(kayer_new);
Console.WriteLine(into_sql);

SqlParameter[] a = SqlParameter_Structure.ADD_T_SQL<User>(kayer_new);
string c = SqlHelper.connectionString;
Console.WriteLine(c);

int i = SqlHelper.ExecteNonQuery(CommandType.Text, into_sql, a);
if (i > 0)
{
Console.WriteLine("执行操作成功");
}
else
{
Console.WriteLine("执行操作失败");
}
*/
#endregion
#region 删除一条记录
User kayer_delete = new User();
kayer_delete.id = 2;

string delete_sql = SQL_Structure.DELETE_T_SQL<User>(kayer_delete);
Console.WriteLine(delete_sql);

SqlParameter[] dedete_p = SqlParameter_Structure.DELETE_T_SQL<User>(kayer_delete);
int i = SqlHelper.ExecteNonQuery(CommandType.Text, delete_sql, dedete_p);
if (i > 0)
{
Console.WriteLine("执行操作成功");
}
else
{
Console.WriteLine("执行操作失败");
}
#endregion
Console.Read();
}
}
}

结果如下：

数据库

未完待续。。。。。