/// <summary> /// 对象参数转换DbParameter /// </summary> /// <returns></returns> public static DbParameter[] GetParameter(Hashtable ht) { IList <DbParameter> parameter = new List <DbParameter>(); DbType dbtype = new DbType(); foreach (string key in ht.Keys) { if (ht[key] is DateTime) { dbtype = DbType.DateTime; } else { dbtype = DbType.String; } parameter.Add(DbParameters.CreateDbParameter(DbParameters.CreateDbParmCharacter() + key, ht[key], dbtype)); } return(parameter.ToArray()); }
/// <summary> /// 对象参数转换DbParameter /// </summary> /// <returns></returns> public static DbParameter[] GetParameter <T>(T entity) { IList <DbParameter> parameter = new List <DbParameter>(); DbType dbtype = new DbType(); Type type = entity.GetType(); PropertyInfo[] props = type.GetProperties(); foreach (PropertyInfo pi in props) { if (pi.GetValue(entity, null) != null) { switch (pi.PropertyType.ToString().ToLower()) { case "system.nullable`1[system.int32]": dbtype = DbType.Int32; break; case "system.nullable`1[system.decimal]": dbtype = DbType.Decimal; break; case "system.nullable`1[system.datetime]": dbtype = DbType.DateTime; break; case "system.nullable`1[system.int64]": dbtype = DbType.Int64; break; case "system.nullable`1[system.boolean]": dbtype = DbType.Boolean; break; default: dbtype = DbType.String; break; } parameter.Add(DbParameters.CreateDbParameter(DbParameters.CreateDbParmCharacter() + pi.GetMappingAttributeName(), pi.GetValue(entity, null), dbtype)); } } return(parameter.ToArray()); }
/// <summary> /// 执行存储过程 存储过程必须带Out的Out_Res参数 /// </summary> /// <param name="dbConnection">连接</param> /// <param name="storedProcName">存储过程名称</param> /// <param name="parameters">入参数</param> /// <returns>Out_Res为Out参数的执行结果</returns> public static object ExecuteByProc(IDbConnection dbConnection, string storedProcName, DbParameter[] parameters, out int rowsAffected) { try { using (IDbConnection conn = dbConnection) { using (IDbCommand cmd = conn.CreateCommand()) { cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = storedProcName; if (conn.State != ConnectionState.Open) { conn.Open(); } if (parameters != null) { parameters = DbParameters.ToDbParameter(parameters); foreach (var parameter in parameters) { cmd.Parameters.Add(parameter); } } cmd.Parameters.Add(DbParameters.CreateDbParameter("@Out_Res", "", DbType.String, 2000, ParameterDirection.Output)); rowsAffected = cmd.ExecuteNonQuery(); DbParameter obj = (DbParameter)cmd.Parameters["@Out_Res"]; //@Out_Res和具体的存储过程参数对应 cmd.Parameters.Clear(); return(Convert.ToString(obj.Value)); } } } catch (DbException ex) { DbLog.WriteLog(ex, storedProcName); if (dbConnection != null) { dbConnection.Close(); } throw ex; } }
/// <summary> /// 泛型方法,反射生成InsertSql语句 /// </summary> /// <param name="entity">实体类</param> /// <returns>int</returns> public static StringBuilder InsertEntitySql <T>(T entity) { Type type = entity.GetType(); StringBuilder sbSql = new StringBuilder(); sbSql.Append($"Insert Into {type.GetMappingAttributeName()} ("); StringBuilder sp = new StringBuilder(); StringBuilder sb_prame = new StringBuilder(); PropertyInfo[] props = type.GetProperties(); foreach (PropertyInfo prop in props) { if (prop.GetValue(entity) != null) { sb_prame.Append($",{prop.GetMappingAttributeName()}"); sp.Append($",{DbParameters.CreateDbParmCharacter() }{prop.GetMappingAttributeName()}");//?ID } } sbSql.Append(sb_prame.ToString().Substring(1, sb_prame.ToString().Length - 1) + ") Values ("); sbSql.Append(sp.ToString().Substring(1, sp.ToString().Length - 1) + ")"); return(sbSql); }
/// <summary> /// 为即将执行准备一个命令 /// </summary> /// <param name="conn">SqlConnection对象</param> /// <param name="cmd">SqlCommand对象</param> /// <param name="isOpenTrans">DbTransaction对象</param> /// <param name="cmdType">执行命令的类型(存储过程或T-SQL,等等)</param> /// <param name="cmdText">存储过程名称或者T-SQL命令行, e.g. Select * from Products</param> /// <param name="dbParameter">执行命令所需的sql语句对应参数</param> private void PrepareCommand(IDbConnection conn, IDbCommand cmd, DbTransaction isOpenTrans, string cmdText, params DbParameter[] dbParameter) { if (conn.State != ConnectionState.Open) { conn.Open(); } cmd.Connection = conn; cmd.CommandText = cmdText; if (isOpenTrans != null) { cmd.Transaction = isOpenTrans; } //cmd.CommandType = cmdType; if (dbParameter != null) { dbParameter = DbParameters.ToDbParameter(dbParameter); foreach (var parameter in dbParameter) { cmd.Parameters.Add(parameter); } } }
/// <summary> /// 返回首行首列 /// </summary> /// <param name="conn"></param> /// <param name="cmdText"></param> /// <param name="parameters"></param> /// <returns></returns> public static object ExecuteScalar(IDbConnection dbConnection, string cmdText, params DbParameter[] parameters) { try { using (IDbConnection conn = dbConnection) { using (IDbCommand cmd = conn.CreateCommand()) { cmd.CommandText = cmdText; if (conn.State != ConnectionState.Open) { conn.Open(); } if (parameters != null) { parameters = DbParameters.ToDbParameter(parameters); foreach (var parameter in parameters) { cmd.Parameters.Add(parameter); } } object obj = cmd.ExecuteScalar(); cmd.Parameters.Clear(); return(obj); } } } catch (DbException ex) { DbLog.WriteLog(ex, cmdText); if (dbConnection != null) { dbConnection.Close(); } throw ex; //return null; } }
/// <summary> /// 执行 SQL 语句,并返回受影响的行数 /// </summary> /// <param name="dbConnection"></param> /// <param name="cmdText"></param> /// <param name="parameters"></param> /// <returns></returns> public static int ExecuteNonQuery(IDbConnection dbConnection, string cmdText, params DbParameter[] parameters) { try { using (var conn = dbConnection) { using (IDbCommand cmd = conn.CreateCommand()) { cmd.CommandText = cmdText; if (conn.State != ConnectionState.Open) { conn.Open(); } if (parameters != null) { parameters = DbParameters.ToDbParameter(parameters); foreach (var parameter in parameters) { cmd.Parameters.Add(parameter); } } int result = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return(result); } } } catch (DbException e) { DbLog.WriteLog(e, cmdText); if (dbConnection != null) { dbConnection.Close(); } throw e; //return -1; } }
public static DataTable ExecuteDataTableByProc(IDbConnection dbConnection, string storedProcName, params DbParameter[] parameters) { try { DataTable dt = new DataTable(); if (dbConnection.State != ConnectionState.Open) { dbConnection.Open(); } using (IDbCommand cmd = dbConnection.CreateCommand()) { cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = storedProcName; if (parameters != null) { parameters = DbParameters.ToDbParameter(parameters); foreach (var parameter in parameters) { cmd.Parameters.Add(parameter); } } IDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);//DataReader关闭时dbConnection也会关闭 cmd.Parameters.Clear(); return(ConvertExtension.IDataReaderToDataTable(dr)); } } catch (DbException ex) { DbLog.WriteLog(ex, storedProcName); if (dbConnection != null) { dbConnection.Close(); } throw ex; } }