public static T Find <T>(int id) where T : Wcs.Models.BaseModel { //using (SqlConnection conn = new SqlConnection(ConfigurationManager.WriteConnString)) using (SqlConnection conn = new SqlConnection(SqlConnectionPool.GetConnection(SqlConnectionPool.SqlConnecctionType.Read))) { conn.Open(); Type type = typeof(T); SqlCommand comm = conn.CreateCommand(); //var attribute = type.GetCustomAttributes(typeof(WcsORMTableNameAttribute), true); //string tableName = string.Empty; //if (attribute != null && attribute.Length > 0) // tableName = ((WcsORMTableNameAttribute)attribute.First()).TableName; string tableName = type.GetMappingName(); //利用静态泛型缓存对sql进行缓存 comm.CommandText = $"{SqlBuilder<T>.GetFindSql()} {id}"; var reader = comm.ExecuteReader(); T t = default(T); if (reader.Read()) { t = (T)Activator.CreateInstance(type); foreach (var item in type.GetProperties()) { item.SetValue(t, reader[item.GetMappingName()]); } } return(t); } }
public void SaveChange() { if (commandList.Count > 0) { using (SqlConnection conn = new SqlConnection(SqlConnectionPool.GetConnection(SqlConnectionPool.SqlConnecctionType.Write))) { conn.Open(); using (SqlTransaction trans = conn.BeginTransaction()) { try { foreach (var item in commandList) { item.Connection = conn; item.Transaction = trans; item.ExecuteNonQuery(); } trans.Commit(); } catch (Exception) { trans.Rollback(); } finally { commandList?.Clear(); } } } } }
public static bool Delete <T>(int id) where T : Wcs.Models.BaseModel { // using (SqlConnection conn = new SqlConnection(ConfigurationManager.WriteConnString)) using (SqlConnection conn = new SqlConnection(SqlConnectionPool.GetConnection(SqlConnectionPool.SqlConnecctionType.Write))) { conn.Open(); Type type = typeof(T); SqlCommand comm = conn.CreateCommand(); comm.CommandText = $"{SqlBuilder<T>.GetFindSql()} {id}"; return(comm.ExecuteNonQuery() > 0); } }
public static bool Update <T>(T t) where T : Wcs.Models.BaseModel { //using (SqlConnection conn = new SqlConnection(ConfigurationManager.WriteConnString)) using (SqlConnection conn = new SqlConnection(SqlConnectionPool.GetConnection(SqlConnectionPool.SqlConnecctionType.Write))) { conn.Open(); Type type = typeof(T); SqlCommand comm = conn.CreateCommand(); int id = (int)type.GetProperty("Id").GetValue(t); SqlParameter[] paraArray = type.GetProperties().Select(s => new SqlParameter($"@{s.GetMappingName()}", s.GetValue(t) ?? DBNull.Value)).ToArray(); comm.CommandText = $"{SqlBuilder<T>.GeUpdateSql()} {id}"; comm.Parameters.AddRange(paraArray); return(comm.ExecuteNonQuery() > 0); } }
public static int Insert <T>(T t) where T : Wcs.Models.BaseModel { //using (SqlConnection conn = new SqlConnection(ConfigurationManager.WriteConnString)) using (SqlConnection conn = new SqlConnection(SqlConnectionPool.GetConnection(SqlConnectionPool.SqlConnecctionType.Write))) { conn.Open(); Type type = typeof(T); SqlCommand comd = conn.CreateCommand(); //扩展方法GetPropertiesWithoutKey把主键过滤掉 //string propString = type.GetPropertiesWithoutKey().Select(s => s.GetMappingName()).Aggregate((x, y) => x + "," + y); //string valueString = type.GetPropertiesWithoutKey().Select(s => $"@{s.GetMappingName()}").Aggregate((x, y) => x + "," + y); //string sql = $@" insert into {type.GetMappingName()}({propString}) values({valueString});select @@identity;"; SqlParameter[] paraArray = type.GetPropertiesWithoutKey().Select(s => new SqlParameter($"@{s.GetMappingName()}", s.GetValue(t) ?? DBNull.Value)).ToArray(); // 利用静态泛型缓存对sql进行缓存 string sql = SqlBuilder <T> .GetInertSql(); comd.CommandText = sql; comd.Parameters.AddRange(paraArray); return(Convert.ToInt32(comd.ExecuteScalar())); } }
/// <summary> /// 按需更新 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="t"></param> /// <returns></returns> public static bool Update <T>(int Id, string json) where T : Wcs.Models.BaseModel { //using (SqlConnection conn = new SqlConnection(ConfigurationManager.WriteConnString)) using (SqlConnection conn = new SqlConnection(SqlConnectionPool.GetConnection(SqlConnectionPool.SqlConnecctionType.Write))) { Type type = typeof(T); T t = JsonConvert.DeserializeObject <T>(json); string tableName = type.GetMappingName(); string sqlUpdata = $@" update {tableName} set {type.GetPropertiesInJson(json).Select(prop => $"{prop.GetMappingName()}=@{prop.GetMappingName()}").Aggregate((x, y) => x + "," + y)} where Id=@Id"; SqlParameter[] paraArray = type.GetPropertiesInJson(json) .Select(s => new SqlParameter($"@{s.GetMappingName()}", s.GetValue(t) ?? DBNull.Value)) .Append(new SqlParameter("@Id", Id)) .ToArray(); conn.Open(); SqlCommand comm = conn.CreateCommand(); comm.CommandText = sqlUpdata; comm.Parameters.AddRange(paraArray); return(comm.ExecuteNonQuery() > 0); } }