反射生成SQL语句

 1 public static int Reg(Model ml)
 2         {
 3             bool b = true;
 4             Visit vt = new Visit();
 5             StringBuilder builder = new StringBuilder("insert into UsersTable (");
 6             Type type = ml.GetType();
 7             PropertyInfo[] per = type.GetProperties();
 8             List<SqlParameter> sqlpar = new List<SqlParameter>();
 9             for (int i = 0; i < per.Length; i++)
10             {
11                 if (!per[i].Name.Equals("Uid") ||!string.IsNullOrEmpty(per[i].GetValue(ml, null).ToString()))
12                 {
13                     if (i == per.Length - 1)
14                     {
15                         if (b == true)
16                         {
17                             builder.Append(per[i].Name + ")values(");
18                             sqlpar.Add(new SqlParameter("@" + per[i].Name, per[i].GetValue(ml, null)));
19                             i = 0;
20                             b = false;
21                         }
22                         else
23                         {
24                             builder.Append("@" + per[i].Name + ")");
25                         }
26                     }
27                     else
28                     {
29                         if (b == true)
30                         {
31                             builder.Append(per[i].Name + ",");
32                             sqlpar.Add(new SqlParameter("@" + per[i].Name, per[i].GetValue(ml, null)));
33                         }
34                         else
35                         {
36                             builder.Append("@"+per[i].Name + ",");
37                         }
38                     }
39                 }
40             }
41             return vt.ExecuteNonQuery(builder.ToString(), sqlpar.ToArray());
42         }

SQL插入

 1 //查询
 2         public static DataSet Login(Model ml)
 3         {
 4             Visit vt = new Visit();
 5             Type type = ml.GetType();
 6             PropertyInfo[] per = type.GetProperties();
 7             List<SqlParameter> sqlpar = new List<SqlParameter>();
 8             StringBuilder builder = new StringBuilder("select Uid from UsersTable where ");
 9             for (int i = 0; i < per.Length; i++)
10             {
11                 if (per[i].GetValue(ml, null)!=null&& !per[i].Name.Equals("Uid"))
12                 {
13                     builder.Append(per[i].Name + "=@" + per[i].Name+" and ");
14                     sqlpar.Add(new SqlParameter("@"+per[i].Name,per[i].GetValue(ml,null)));
15                 }
16                 
17             }
18             string sqlStr = builder.ToString().Substring(0, builder.ToString().LastIndexOf("and"));
19             return vt.SqlDataAdapter(sqlStr, sqlpar.ToArray());
20         }

SQL查询

 1 public static bool upDate(Model ml)
 2         {
 3             Visit vt = new Visit();
 4             StringBuilder sbuilder = new StringBuilder("Update UsersTable set ");
 5             
 6             Type ModelType = ml.GetType();
 7             PropertyInfo[] ProPer = ModelType.GetProperties();
 8             List<SqlParameter> sqlpara = new List<SqlParameter>();
 9             
10             foreach (PropertyInfo item in ProPer)
11             {
12                 if (!string.IsNullOrEmpty(item.GetValue(ml,null).ToString())&& !item.Name.Equals("Uid"))
13                 {
14                     sbuilder.Append(item.Name + "=@" + item.Name+",");
15                     sqlpara.Add(new SqlParameter("@" + item.Name, item.GetValue(ml, null)));
16                 }
17             }
18             string str = sbuilder.ToString().Trim(',');
19             str += " where Uid=@Uid";
20             sqlpara.Add(new SqlParameter("@Uid", ml.Uid));
21             return vt.ExecuteNonQuery(str, sqlpara.ToArray()) > 0;
22             
23         }

SQL更新

 

 

转载于:https://www.cnblogs.com/2013likong/p/3477114.html