数据库基础操作
public static string DefaultConnString
{
get
{
return ConfigurationManager.AppSettings["Connection"];
} }
/// /// 构造事务 ///
public SqlTransaction GetTransaction()
{
using (SqlConnection connection = new SqlConnection(this.SqlConnString))
{
connection.Open();
SqlTransaction trans = connection.BeginTransaction();
return trans;
} } ///
/// 构造连接 ///
public SqlConnection GetConnection()
{ return new SqlConnection(this.SqlConnString); }
/// /// 关闭连接 ///
public void ConnClose(SqlConnection connection)
{ if (connection.State == ConnectionState.Open) { connection.Close(); } }
/// /// 执行SQL命令，无返回值 ///
public int ExecuteNonQuery(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{ SqlCommand cmd = new SqlCommand();
using (SqlConnection conn = new SqlConnection(this.SqlConnString))
{ PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear(); return val;
} }
/// /// 执行SQL命令，无返回值 ///
public int ExecuteNonQuery(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val; }
/// /// 执行SQL命令，无返回值 ///
public int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{ SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val; }
/// /// 执行SQL命令，返回SqlDataReader ///
public SqlDataReader ExecuteReader(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{ SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(this.SqlConnString);
// we use a try/catch here because if the method throws an exception we want to // close the connection throw code, because no datareader will exist, hence the // commandBehaviour.CloseConnection will not work
try { PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear(); return rdr; }
catch (Exception ex) { conn.Close(); throw ex; } }
/// /// 执行SQL命令，返回String ///
public string ExecuteScalar(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{ SqlCommand cmd = new SqlCommand();
using (SqlConnection connection = new SqlConnection(this.SqlConnString))
{ PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar(); cmd.Parameters.Clear();
if (val == null) return string.Empty;
else return val.ToString(); } }
/// /// 执行SQL命令，返回String ///
public string ExecuteScalar(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{ SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar(); cmd.Parameters.Clear();
if (val == null) return string.Empty;
else return val.ToString(); }
/// /// 执行SQL命令，返回String ///
public string ExecuteScalar(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{ SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
if (val == null) return string.Empty; else return val.ToString(); }
/// /// 执行SQL命令，返回DataTable ///
public DataTable ExecuteDataTable(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{ SqlCommand cmd = new SqlCommand();
DataTable oTab = new DataTable(cmdText);
using (SqlConnection connection = new SqlConnection(this.SqlConnString))
{ PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
SqlDataAdapter adp = new SqlDataAdapter(cmd);
adp.Fill(oTab); }
return oTab;
}
/// /// 缓存参数 ///
public void CacheParameters(string cacheKey, params SqlParameter[] commandParameters)
{ parmCache[cacheKey] = commandParameters; }
/// /// 读取缓存参数 ///
public SqlParameter[] GetCachedParameters(string cacheKey)
{ SqlParameter[] cachedParms = (SqlParameter[])parmCache[cacheKey];
if (cachedParms == null) return null;
SqlParameter[] clonedParms = new SqlParameter[cachedParms.Length];
for (int i = 0, j = cachedParms.Length; i < j; i++) clonedParms[i] = (SqlParameter)((ICloneable)cachedParms[i]).Clone(); return clonedParms;
}
/// /// 执行SQL命令 ///
private void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
{
if (conn.State != ConnectionState.Open) conn.Open();
cmd.Connection = conn; cmd.CommandText = cmdText;
cmd.CommandTimeout = 120;
if (trans != null) cmd.Transaction = trans;
cmd.CommandType = cmdType;
if (cmdParms != null) { foreach (SqlParameter parm in cmdParms) cmd.Parameters.Add(parm);
} } }
DataBase类 /// /// 数据库参数构造 ///
public static SqlParameter MakeInParam(string parameterName, SqlDbType dbType, int size)
{ SqlParameter sqlPar = new SqlParameter(parameterName, dbType, size); return sqlPar;
}
/// /// 数据库参数构造 ///
public static SqlParameter MakeInParam(string parameterName, SqlDbType dbType, int size, object oValue)
{ SqlParameter sqlPar = new SqlParameter(parameterName, dbType, size); sqlPar.Value = oValue; return sqlPar;
}
/// /// 数据库参数构造 ///
public static SqlParameter MakeInParam(string parameterName, SqlDbType dbType, int size, string sValue)
{ SqlParameter sqlPar = new SqlParameter(parameterName, dbType, size); sqlPar.Value = sValue; return sqlPar; }
/// /// 数据库参数构造 ///
public static SqlParameter MakeInParam(string parameterName, SqlDbType dbType, int size, int iValue)
{ SqlParameter sqlPar = new SqlParameter(parameterName, dbType, size); sqlPar.Value = iValue; return sqlPar; }
/// /// 数据库参数构造 ///
public static SqlParameter MakeInParam(string parameterName, SqlDbType dbType, int size, DateTime dValue)
{
SqlParameter sqlPar = new SqlParameter(parameterName, dbType, size);
if (dValue == DateTime.MaxValue) sqlPar.Value = DBNull.Value;
else sqlPar.Value = dValue; return sqlPar;
}
/// /// 数据库参数构造 ///
public static SqlParameter MakeInParam(string parameterName, SqlDbType dbType, int size, double dValue) { SqlParameter sqlPar = new SqlParameter(parameterName, dbType, size); sqlPar.Value = dValue; return sqlPar; }
/// /// 数据库参数构造 ///
public static SqlParameter MakeInParam(string parameterName, SqlDbType dbType, int size, bool? bValue)
{
SqlParameter sqlPar = new SqlParameter(parameterName, dbType, size);
if (bValue.HasValue) sqlPar.Value = bValue;
else sqlPar.Value = DBNull.Value; return sqlPar;
}
/// /// 转换数据类型 ///
public static string ValueToString(object oValue)
{
if (oValue == System.DBNull.Value)
return "";
else return oValue.ToString();
}
/// /// 转换数据类型 ///
public static DateTime ValueToDateTime(object oValue)
{
if (oValue == System.DBNull.Value) return DateTime.MinValue; else return Convert.ToDateTime(oValue);
}
/// /// 转换数据类型 ///
public static int ValueToInt(object oValue)
{
if (oValue == System.DBNull.Value) return int.MinValue; else return Convert.ToInt32(oValue);
}
public OrgDepartment GetOrgDepartmentNameByGuid(string guid)
{
OrgDepartment department = new OrgDepartment();
SqlParameter[] prams = {
Database.MakeInParam("@DepartmentGuid", System.Data.SqlDbType.UniqueIdentifier, 16, new Guid(guid)),
};
using (SqlDataReader read = new SqlPlus().ExecuteReader(CommandType.StoredProcedure, "up_GetDepartmentByGuid", prams))
{
while (read.Read())
{
department.DepartmentGuid = new Guid(read["DepartmentGuid"].ToString());
department.DepartmentName = read["DepartmentName"].ToString();
} }
return department; }
