(string sql); /// /// QueryModel 无参 /// T QueryModel(string sql) where T : new(); /// /// QueryList 无参 /// List QueryList(string sql) where T : new(); #endregion
#region 有参 /// /// Query 有参 /// DataTable Query(string sql, object para); /// /// QueryObject 有参 /// T QueryObject(string sql, object para); /// /// QueryModel 有参 /// T QueryModel(string sql, object para) where T : new(); /// /// QueryList 有参 /// List QueryList(string sql, object para) where T : new(); #endregion
public T QueryObject(string sql) { T t; DataTable dt = BaseQuery(sql); if (dt != null && dt.Rows.Count > 0) { object result = dt.Rows[0][0]; t = (T)result; } else { t = default(T); } return t; }
public T QueryModel(string sql) where T : new() { T t; DataTable dt = BaseQuery(sql); if (dt != null && dt.Rows.Count > 0) { t = new T(); DataRow dr = dt.Rows[0]; PropertyInfo[] propertys = t.GetType().GetProperties(); foreach (PropertyInfo pi in propertys) { string tempName = pi.Name; if (dt.Columns.Contains(tempName)) { if (!pi.CanWrite) continue; object value = dr[tempName]; if (value != DBNull.Value) pi.SetValue(t, value, null); } } } else { t = default(T); } return t; }
public List QueryList(string sql) where T : new() { List tList; DataTable dt = BaseQuery(sql); if (dt != null && dt.Rows.Count > 0) { tList = new List(); foreach (DataRow dr in dt.Rows) { T t = new T(); PropertyInfo[] propertys = t.GetType().GetProperties(); foreach (PropertyInfo pi in propertys) { string tempName = pi.Name; if (dt.Columns.Contains(tempName)) { if (!pi.CanWrite) continue; object value = dr[tempName]; if (value != DBNull.Value) pi.SetValue(t, value, null); } } tList.Add(t); } } else { tList = new List(); } return tList; } #endregion
public T QueryObject(string sql, object para) { T t; DataTable dt = BaseQuery(sql, para); if (dt != null && dt.Rows.Count > 0) { object result = dt.Rows[0][0]; t = (T)result; } else { t = default(T); } return t; }
public T QueryModel(string sql, object para) where T : new() { T t; DataTable dt = BaseQuery(sql, para); if (dt != null && dt.Rows.Count > 0) { t = new T(); DataRow dr = dt.Rows[0]; PropertyInfo[] propertys = t.GetType().GetProperties(); foreach (PropertyInfo pi in propertys) { string tempName = pi.Name; if (dt.Columns.Contains(tempName)) { if (!pi.CanWrite) continue; object value = dr[tempName]; if (value != DBNull.Value) pi.SetValue(t, value, null); } } } else { t = default(T); } return t; }
public List QueryList(string sql, object para) where T : new() { List tList; DataTable dt = BaseQuery(sql, para); if (dt != null && dt.Rows.Count > 0) { tList = new List(); foreach (DataRow dr in dt.Rows) { T t = new T(); PropertyInfo[] propertys = t.GetType().GetProperties(); foreach (PropertyInfo pi in propertys) { string tempName = pi.Name; if (dt.Columns.Contains(tempName)) { if (!pi.CanWrite) continue; object value = dr[tempName]; if (value != DBNull.Value) pi.SetValue(t, value, null); } } tList.Add(t); } } else { tList = new List(); } return tList; } #endregion
List paraName = new List(); string sqlTag = ToWhat.SqlInit(sql, out paraName);
SqlParameter[] sqlParas = new SqlParameter[paraName.Count]; for (int i = 0; i < paraName.Count; i++) { string key = paraName[i]; object value = ht[key]; sqlParas[i] = new SqlParameter("@" + key, value); } var result = helper.ExecuteReader(sqlTag, sqlParas); return result; } else if (typeName.Contains("Hashtable"))//Hashtanle入参 { ht = (Hashtable)para;
List paraName = new List(); string sqlTag = ToWhat.SqlInit(sql, out paraName);
SqlParameter[] sqlParas = new SqlParameter[paraName.Count]; for (int i = 0; i < paraName.Count; i++) { string key = paraName[i]; object value = ht[key]; sqlParas[i] = new SqlParameter("@" + key, value); } var result = helper.ExecuteReader(sqlTag, sqlParas); return result; } else//一个参数入参 { List paraName = new List(); string sqlTag = ToWhat.SqlInit(sql, out paraName); SqlParameter[] sqlParas = new SqlParameter[paraName.Count]; for (int i = 0; i < paraName.Count; i++) { string key = paraName[i]; object value = para; sqlParas[i] = new SqlParameter("@" + key, value); } var result = helper.ExecuteReader(sqlTag, sqlParas); return result; } } #endregion #endregion
/// /// 用户新增 /// public const string UserInsert = "insert into [User](UserName,Password) values(#UserName#,#Password#)";
参数用##包起来。 SqlInit就是用来获取sql语句需要的参数名和初始化sql语句。
public static string SqlInit(string sql, out List paraName) { string sqlTag = sql; List sqlParaName = new List(); Regex regex = new Regex("(#(.[^#]+)#)"); var ms = regex.Matches(sql); foreach (Match item in ms) { var p1 = item.Groups[1]; var p2 = item.Groups[2]; sqlTag = sqlTag.Replace(p1.Value, "@" + p2.Value); sqlParaName.Add(p2.Value); } paraName = sqlParaName; return sqlTag; }