本篇接C# SQL（二）
在其代码上继续扩展 删操作 ， 让我们开始吧。。。。
一 ： 构建T-SQL语句（在SQL_Structure.cs中添加一个方法 DELETE_T_SQL ， 用于构建delete的T-SQL）
using DBDome.model;using System;using System.Collections.Generic;using System.Linq;using System.Reflection;using System.Text;namespace DBDome.com{ /// /// SQL构建器(还没与赋值) /// public sealed class SQL_Structure { private SQL_Structure() { } /// /// 添加一个数据的T-SQL的构成 /// /// /// /// ( T model ) where T : BaseModel { string base_add = @"insert into [{0}] ({1}) values ({2})";//表名 ， 列明 ， 对应的值 Type model_type = model.GetType(); string[] arr = model_type.Name.Split(new char[] { '.' }); string table_name = arr[arr.Length - 1];//获得表的名称 PropertyInfo[] p_intos = model_type.GetProperties(); StringBuilder table_field_name = new StringBuilder(); StringBuilder table_field_value = new StringBuilder(); PropertyInfo item = null; for (int i = 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 + ","); } } return String.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)); } /// /// 删除一条数据的T-SQL构成(以ID号来删除) /// /// /// /// (T model) where T : BaseModel { string base_delete = @"delete from [{0}] where {1} = {2}"; Type model_type = model.GetType(); string[] arr = model_type.Name.Split(new char[] { '.' }); string table_name = arr[arr.Length - 1];//获得表的名称 PropertyInfo[] p_intos = model_type.GetProperties(); PropertyInfo item = null; for (int i = 0; i < p_intos.Length; i++) { item = p_intos[i]; if (item.Name == "id") { //返回T-SQL构建 return string.Format( base_delete , table_name, item.Name, "@" + item.Name ); } } return string.Empty; } }} 二 ： 构建SqlParameter参数 （在SqlParameter_Structure.as中添加方法DELETE_T_SQL）
using DBDome.model;using System;using System.Collections.Generic;using System.Data.SqlClient;using System.Linq;using System.Reflection;using System.Text;using System.Data;namespace DBDome.com{ /// /// SqlParameter /// public sealed class SqlParameter_Structure { private SqlParameter_Structure() { } /// /// 构建T-SQL参数 ADD /// /// /// /// (T model) where T : BaseModel { Type model_type = model.GetType(); PropertyInfo[] p_intos = model_type.GetProperties(); SqlParameter[] sql_param = new SqlParameter[p_intos.Length - 1];//不需要主键 PropertyInfo item = null; string p_name = ""; SqlParameter cell = null; int j = 0; for (int i = 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 = new SqlParameter(p_name, SqlParameter_Structure.Get_SqlDbType_SqlType(item.PropertyType)); cell.Value = item.GetValue(model, null); sql_param[j] = cell; j++; } return sql_param; } /// /// 构建T-SQL参数 DELETE /// /// /// /// (T model) where T : BaseModel { Type model_type = model.GetType(); PropertyInfo[] p_intos = model_type.GetProperties(); SqlParameter[] sql_param = new SqlParameter[1];//只需要主键 PropertyInfo item = null; for (int i = 0; i < p_intos.Length; i++) { item = p_intos[i]; if (item.Name != "id") continue; SqlParameter cell = new SqlParameter(item.Name, SqlParameter_Structure.Get_SqlDbType_SqlType(item.PropertyType)); cell.Value = item.GetValue(model, null); sql_param[0] = cell; break; } return sql_param; } private static SqlDbType Get_SqlDbType_SqlType(Type cshaper) { string[] arr = cshaper.ToString().Split(new char[] { '.' }); string toLow = arr[arr.Length - 1].ToLower(); switch (toLow) { case "string": return SqlDbType.NVarChar; break; case "int16": return SqlDbType.Bit; break; case "int32": return SqlDbType.Int; break; default: throw new Exception(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(); } }}结果如下：
数据库
未完待续。。。。。