数据库基础操作

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; }

转载于:https://www.cnblogs.com/GreenGrass/archive/2012/12/06/2804892.html