最近写一个项目,我开发的方式首先设计数据库,然后用网上免费的代码生成软件CodePlus V2.0生成mode对象,同时封装操作方法,在与数据库操作方面我封装了一个数据操作类,很方便,贡献出来。
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
Code
public static class SqlPlus
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
// Hashtable to store cached parameters
private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public static string connectionString = "Data Source=spsserver;Initial Catalog=Cooec;uid=sa;pwd=ssssaaaa";
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public static SqlTransaction GetTransaction()
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
using (SqlConnection connection = new SqlConnection(SqlPlus.connectionString))
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
connection.Open();
SqlTransaction trans = connection.BeginTransaction();
return trans;
}
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public static void ConnClose(SqlConnection connection)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (connection.State == ConnectionState.Open)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
connection.Close();
}
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public static int ExecuteNonQuery(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
SqlCommand cmd = new SqlCommand();
using (SqlConnection conn = new SqlConnection(SqlPlus.connectionString))
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public static int ExecuteNonQuery(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
SqlCommand cmd = new SqlCommand();
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public static SqlDataReader ExecuteReader(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(SqlPlus.connectionString);
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
// 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
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return rdr;
}
catch
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
conn.Close();
throw;
}
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public static object ExecuteScalar(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
SqlCommand cmd = new SqlCommand();
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
using (SqlConnection connection = new SqlConnection(SqlPlus.connectionString))
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public static object ExecuteScalar(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
SqlCommand cmd = new SqlCommand();
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public static DataTable ExecuteDataTable(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
SqlCommand cmd = new SqlCommand();
DataTable oTab = new DataTable();
using (SqlConnection connection = new SqlConnection(SqlPlus.connectionString))
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
SqlDataAdapter adp = new SqlDataAdapter(cmd);
adp.Fill(oTab);
}
return oTab;
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public static void CacheParameters(string cacheKey, params SqlParameter[] commandParameters)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
parmCache[cacheKey] = commandParameters;
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public static SqlParameter[] GetCachedParameters(string cacheKey)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
SqlParameter[] cachedParms = (SqlParameter[])parmCache[cacheKey];
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
if (cachedParms == null)
return null;
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
SqlParameter[] clonedParms = new SqlParameter[cachedParms.Length];
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
for (int i = 0, j = cachedParms.Length; i < j; i++)
clonedParms[i] = (SqlParameter)((ICloneable)cachedParms[i]).Clone();
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
return clonedParms;
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
if (conn.State != ConnectionState.Open)
conn.Open();
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
cmd.Connection = conn;
cmd.CommandText = cmdText;
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
if (trans != null)
cmd.Transaction = trans;
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
cmd.CommandType = cmdType;
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
if (cmdParms != null)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
foreach (SqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
}
}
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
public static class Database
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
public static SqlParameter MakeInParam(string parameterName, SqlDbType dbType, int size)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
SqlParameter sqlPar = new SqlParameter(parameterName, dbType, size);
return sqlPar;
}
public static SqlParameter MakeInParam(string parameterName, SqlDbType dbType, int size, object oValue)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
SqlParameter sqlPar = new SqlParameter(parameterName, dbType, size);
sqlPar.Value = oValue;
return sqlPar;
}
public static SqlParameter MakeInParam(string parameterName, SqlDbType dbType, int size, string sValue)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
SqlParameter sqlPar = new SqlParameter(parameterName, dbType, size);
sqlPar.Value = sValue;
return sqlPar;
}
public static SqlParameter MakeInParam(string parameterName, SqlDbType dbType, int size, int iValue)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
SqlParameter sqlPar = new SqlParameter(parameterName, dbType, size);
sqlPar.Value = iValue;
return sqlPar;
}
public static SqlParameter MakeInParam(string parameterName, SqlDbType dbType, int size, DateTime dValue)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
SqlParameter sqlPar = new SqlParameter(parameterName, dbType, size);
if (dValue == DateTime.MinValue)
sqlPar.Value = DBNull.Value;
else
sqlPar.Value = dValue;
return sqlPar;
}
public static SqlParameter MakeInParam(string parameterName, SqlDbType dbType, int size, double dValue)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
SqlParameter sqlPar = new SqlParameter(parameterName, dbType, size);
sqlPar.Value = dValue;
return sqlPar;
}
public static string ValueToString(object oValue)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (oValue == System.DBNull.Value)
return "";
else
return oValue.ToString();
}
public static DateTime ValueToDateTime(object oValue)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (oValue == System.DBNull.Value)
return DateTime.MinValue;
else
return Convert.ToDateTime(oValue);
}
public static int ValueToInt(object oValue)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (oValue == System.DBNull.Value)
return int.MinValue;
else
return Convert.ToInt32(oValue);
}
}