/// <summary>
/// 执行更新操作
/// </summary>
/// <param name="connectionString"></param>
/// <param name="creator"></param>
/// <returns></returns>
public static int Update(string connectionString, SqlCreator creator)
{
string sql = creator.GetUpdateSql();
var par = new SqlParameter[creator.Columns.Length + creator.WhereColumns.Length];
for (int i = 0; i < creator.Columns.Length; i++)
{
Column parm = creator.Columns[i];
par[i] = MakeInputParam(parm.ParameterName, parm.DbType, parm.Size, parm.Value);
}
for (int i = 0; i < creator.WhereColumns.Length; i++)
{
Column parm = creator.WhereColumns[i];
par[i + creator.Columns.Length] = MakeInputParam(parm.ParameterName, parm.DbType, parm.Size, parm.Value);
}
return ExecuteNonQuery(connectionString, CommandType.Text, sql, par);
}
/// <summary>
/// 执行选择操作，使用通用存储过程
/// </summary>
/// <param name="connectionString"></param>
/// <param name="condition"></param>
/// <param name="creator"></param>
/// <returns></returns>
public static SqlDataReader SelectReader(string connectionString, string condition,
SqlCreator creator, PageParam page, out SqlParameter count,
out SqlParameter total)
{
if (string.IsNullOrEmpty(creator.PrimaryKey))
{
throw new Exception("必须为Creator对象指定PrimaryKey属性");
}
string orderBy = "";
if (!string.IsNullOrEmpty(page.OrderBy))
{
orderBy = page.OrderBy + " " + page.OrderType;
}
var par = new[]
{
MakeOutputParam("@PageCount", SqlDbType.Int, 4),
MakeOutputParam("@TotalRecords", SqlDbType.Int, 4),
MakeInputParam("@TableName", SqlDbType.VarChar, 255, creator.TableName),
MakeInputParam("@PrimaryKey", SqlDbType.VarChar, 255, creator.PrimaryKey),
MakeInputParam("@Condition", SqlDbType.VarChar, 2000, condition),
MakeInputParam("@OrderBy", SqlDbType.VarChar, 500, orderBy),
MakeInputParam("@GroupBy", SqlDbType.VarChar, 255, creator.GroupBy),
MakeInputParam("@PageIndex", SqlDbType.Int, 4, page.Index),
MakeInputParam("@PageSize", SqlDbType.Int, 4, page.Size)
};
count = par[0];
total = par[1];
return ExecuteReader(connectionString, CommandType.StoredProcedure, "SingleTableCommonPager", par);
}
/// <summary>
/// 执行插入操作
/// </summary>
/// <param name="connectionString"></param>
/// <param name="creator"></param>
/// <returns></returns>
/// <example>
/// </example>
public static int Insert(string connectionString, SqlCreator creator)
{
string sql = creator.GetInsertSql();
var par = new SqlParameter[creator.Columns.Length + 1];
par[0] = MakeOutputParam("@Id", SqlDbType.Int, 4);
for (int i = 0; i < creator.Columns.Length; i++)
{
Column parm = creator.Columns[i];
par[i + 1] = MakeInputParam(parm.ParameterName, parm.DbType, parm.Size, parm.Value);
}
if (ExecuteNonQuery(connectionString, CommandType.Text, sql, par) > 0)
{
try
{
return Convert.ToInt32(par[0].Value);
}
catch
{
return -1;
}
}
else
{
return 0;
}
}
/// <summary>
/// 执行选择操作，返回SqlDataReader
/// </summary>
/// <param name="connectionString"></param>
/// <param name="creator"></param>
/// <returns></returns>
public static SqlDataReader SelectReader(string connectionString, SqlCreator creator)
{
string sql = creator.GetSelectSql();
var par = new SqlParameter[creator.WhereColumns.Length];
for (int i = 0; i < creator.WhereColumns.Length; i++)
{
Column parm = creator.WhereColumns[i];
par[i] = MakeInputParam(parm.ParameterName, parm.DbType, parm.Size, parm.Value);
}
return ExecuteReader(connectionString, CommandType.Text, sql, par);
}
/// <summary>
/// 判断是否存在一条记录
/// </summary>
/// <param name="connectionString">连接字符串</param>
/// <param name="creator">判断条件</param>
/// <returns></returns>
public static bool Exists(string connectionString, SqlCreator creator)
{
creator.Columns = new[]
{
new Column("count(1)")
};
var par = new SqlParameter[creator.WhereColumns.Length];
for (int i = 0; i < creator.WhereColumns.Length; i++)
{
Column parm = creator.WhereColumns[i];
par[i] = MakeInputParam(parm.ParameterName, parm.DbType, parm.Size, parm.Value);
}
object obj = ExecuteScalar(connectionString, CommandType.Text,
creator.GetSelectSql(),
par);
int count = int.Parse(obj.ToString());
return count > 0;
}