/// <summary>
/// add by peter
/// date 2010-11-18
/// </summary>
/// <param name="v_sQueryString"></param>
/// <param name="v_oSqlParams"></param>
public void ExecuteQueryString(string v_sQueryString, SqlParameter[] v_oSqlParams)
{
SqlTransConn transConn = GetConnection();
try
{
using (SqlCommand command = transConn.CreateCommand())
{
command.CommandText = v_sQueryString;
command.CommandType = CommandType.Text;
foreach (SqlParameter parm in v_oSqlParams)
{
command.Parameters.Add(parm);
}
command.ExecuteNonQuery();
command.Parameters.Clear();
}
}
catch (Exception ex)
{
throw new Exception(v_sQueryString, ex);
}
finally
{
transConn.Close();
}
}
/// <summary>
/// return the value of the parameter which is sqldbDirection.outPut
/// </summary>
/// <param name="v_sStoreProcName"></param>
/// <param name="v_oSqlParams"></param>
/// <returns></returns>
public int ExecuteProc(Query query)
{
List <SqlParameter> parameters = new List <SqlParameter>();
SqlTransConn transConn = GetConnection();
string storeProcName = query.Procedure;
// generic, no mapping
// rely on input parameter type
foreach (string key in query.Parameters.Keys)
{
SqlParameter parameter = new SqlParameter(key, query.Parameters[key].Type);
parameter.Value = query.Parameters[key].Value;
parameter.Direction = query.Parameters[key].Direction;
parameters.Add(parameter);
}
int result;
try
{
result = SqlHelper.ExecuteNonQuery(transConn, CommandType.StoredProcedure, storeProcName, parameters);
}
catch (Exception ex)
{
throw new Exception("Failed to execute store proc:" + storeProcName, ex);
}
finally
{
transConn.Close();
}
return(result);
}
public bool IsReadOnly()
{
string sql = @"SELECT is_read_only FROM sys.databases WHERE name = @name";
List <SqlParameter> parameters = new List <SqlParameter>();
SqlTransConn transConn = GetConnection(_connectionName);
int result = 0;
try
{
using (SqlCommand command = transConn.CreateCommand())
{
command.CommandText = sql;
command.CommandType = CommandType.Text;
command.Parameters.Add(new SqlParameter("@name", transConn.Conn.Database));
result = System.Convert.ToInt32(command.ExecuteScalar());
}
}
catch (Exception ex)
{
throw new Exception("Failed to execute sql:" + sql, ex);
}
finally
{
if (!transConn.IsInTransaction())
{
transConn.Close();
}
}
return(result > 0);
}
private List <T> _findAllFromStoreProc(string storeProcName, List <SqlParameter> parameters)
{
List <T> list = new List <T>();
SqlTransConn transConn = GetConnection(_connectionName);
try
{
using (SqlDataReader reader = SqlHelper.ExecuteReader(transConn, CommandType.StoredProcedure, storeProcName, parameters))
{
if (reader.HasRows)
{
while (reader.Read())
{
T to = new T();
to.Initialize(reader);
//LateBinding(ref to, query);
list.Add(to);
}
}
}
}
catch (Exception ex)
{
throw new Exception("Failed to execute store proc:" + storeProcName, ex);
}
finally
{
if (!transConn.IsInTransaction())
{
transConn.Close();
}
}
return(list);
}
// does not work
// public int Delete(Query query)
// {
// SqlBuilder builder = InitSqlBuilder(query);
// String sql = builder.BuildDelete();
// int result;
//#if DEBUG
// m_log.Error(sql);
//#endif
// SqlTransConn transConn = GetConnection();
// try
// {
// using (SqlCommand command = transConn.CreateCommand())
// {
// command.CommandText = sql;
// command.CommandType = CommandType.Text;
// List<SqlParameter> parameters = builder.GetSqlParams();
// foreach (SqlParameter parm in parameters)
// command.Parameters.Add(parm);
// result = command.ExecuteNonQuery();
// command.Parameters.Clear();
// }
// }
// catch (Exception ex)
// {
// throw new Exception(sql, ex);
// }
// finally
// {
// transConn.Close();
// }
// return result;
// }
private void _updateOrAddOrDeleteDBFromBuilder(Query query)
{
SqlBuilder builder = InitSqlBuilder(query);
String sql = builder.Build();
SqlTransConn transConn = GetConnection();
try
{
using (SqlCommand command = transConn.CreateCommand())
{
command.CommandText = sql;
command.CommandType = CommandType.Text;
List <SqlParameter> parameters = builder.GetSqlParams();
foreach (SqlParameter parm in parameters)
{
command.Parameters.Add(parm);
}
command.ExecuteNonQuery();
command.Parameters.Clear();
}
}
catch (Exception ex)
{
throw new Exception(sql, ex);
}
finally
{
transConn.Close();
}
}
public int DeleteByPK(object PKValue)
{
StringBuilder sb = new StringBuilder();
sb.Append("Delete from ").Append(TableName);
sb.Append(" where ").Append(PrimaryKey).Append("=@PKValue");
List <SqlParameter> parameters = new List <SqlParameter>();
parameters.Add(new SqlParameter("@PKValue", PKValue));
SqlTransConn transConn = GetConnection();
int result = 0;
try
{
result = SqlHelper.ExecuteNonQuery(transConn, CommandType.Text, sb.ToString(), parameters);
}
catch (Exception ex)
{
throw new Exception(ex.Message + ":" + sb.ToString(), ex);
}
finally
{
transConn.Close();
}
return(result);
}
public bool ExecuteNonQuery(string v_sQueryString, SqlParameter[] v_oSqlParams, out int count)
{
bool bol = false;
count = 0;
SqlTransConn transConn = GetConnection();
try
{
count = SqlHelper.ExecuteNonQuery(transConn, CommandType.Text, v_sQueryString, v_oSqlParams != null ? v_oSqlParams.ToList() : null);
if (count > 0)
{
bol = true;
}
}
catch (Exception ex)
{
throw new Exception(v_sQueryString, ex);
}
finally
{
transConn.Close();
}
return(bol);
}
/// <summary>
/// return the value of the parameter which is sqldbDirection.outPut
/// </summary>
/// <param name="v_sStoreProcName"></param>
/// <param name="v_oSqlParams"></param>
/// <returns></returns>
public string ExecuteProcAndGetOutPut(string v_sStoreProcName, SqlParameter[] v_oSqlParams)
{
string v_sReturnValue = "";
int v_iOutputIndex = 0;
List <SqlParameter> parameters = new List <SqlParameter>();
SqlTransConn transConn = GetConnection();
foreach (SqlParameter pram in v_oSqlParams)
{
if (pram.Direction == ParameterDirection.Output || pram.Direction == ParameterDirection.ReturnValue)
{
v_iOutputIndex = parameters.Count;
}
parameters.Add(pram);
}
try
{
SqlHelper.ExecuteNonQuery(transConn, CommandType.StoredProcedure, v_sStoreProcName, parameters);
v_sReturnValue = parameters[v_iOutputIndex].Value.ToString();
}
catch (Exception ex)
{
throw new Exception("Failed to execute store proc:" + v_sStoreProcName, ex);
}
finally
{
transConn.Close();
}
return(v_sReturnValue);
}
public static List <T> ExecuteReader <T>(SqlTransConn transConn, CommandType cmdType, string cmdText, List <SqlParameter> parameters) where T : BaseTO, new()
{
List <T> list = new List <T>();
try
{
using (SqlDataReader reader = SqlHelper.ExecuteReader(transConn, cmdType, cmdText, parameters))
{
if (reader.HasRows)
{
while (reader.Read())
{
T to = new T();
to.Initialize(reader);
list.Add(to);
}
}
}
}
catch (Exception ex)
{
throw new Exception("Failed to execute sql:" + cmdText, ex);
}
finally
{
transConn.Close();
}
return(list);
}
/// <summary>
/// 根据主键数组和给定实体的值更新
/// </summary>
/// <param name="to"></param>
/// <param name="ids"></param>
/// <param name="enablePKUpdate">是否允许更新主键，用于某些自定义主键需要更新的情况</param>
public int UpdateFromIds(BaseTO to, long[] ids, bool enablePKUpdate)
{
Dictionary <string, object> dict;
dict = to.GetSQLWriteValues();
//我把recordcount放在baseTo里面，用于列表分页时返回记录总数，这里新增修改时需要过滤掉 kevin
dict.Remove("recordcount");
StringBuilder sb = new StringBuilder();
sb.Append("update ").Append(_tableName).Append(" set ");
List <SqlParameter> parameters = new List <SqlParameter>();
int i = 0;
string[] primaryKeys = to.GetPrimaryKey <T>();
foreach (string name in dict.Keys)
{
//如果不允许更新主键，不是主键才添加到update语句的更新字段中
if (enablePKUpdate ? true : !primaryKeys.Contains <string>(name))
{
if (i > 0)
{
sb.Append(", ");
}
sb.Append(name).Append("=@").Append(name);
parameters.Add(Query.BuilderParam(name, dict[name]));
i++;
}
}
if (null == primaryKeys || 0 == primaryKeys.Length)
{
throw new ExceptionNoPrimaryKey();
}
string idList = string.Join(",", ids);
sb.Append(" where " + primaryKeys[0] + " in ( " + idList + ")");
SqlTransConn transConn = GetConnection(_connectionName);
int result = 0;
try
{
result = SqlHelper.ExecuteNonQuery(transConn, CommandType.Text, sb.ToString(), parameters);
}
catch (Exception ex)
{
throw new Exception(ex.Message + ":" + sb.ToString(), ex);
}
finally
{
if (!transConn.IsInTransaction())
{
transConn.Close();
}
}
return(result);
}
private List <T> _findAllFromBuilder(Query query)
{
List <T> list = new List <T>();
SqlBuilder builder;
String sql;
try
{
builder = InitSqlBuilder(query);
sql = builder.Build();
}
catch (ExceptionEmptyQuery)
{
return(new List <T>());
}
SqlTransConn transConn = GetConnection();
try
{
using (SqlCommand command = transConn.CreateCommand())
{
command.CommandText = sql;
command.CommandType = CommandType.Text;
List <SqlParameter> parameters = builder.GetSqlParams();
foreach (SqlParameter parm in parameters)
{
command.Parameters.Add(parm);
}
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
T to = new T();
to.Initialize(reader);
LateBinding(ref to, query);
list.Add(to);
}
}
}
command.Parameters.Clear();
}
}
catch (Exception ex)
{
throw new Exception(sql, ex);
}
finally
{
transConn.Close();
}
return(list);
}
/// <summary>
/// Close and abort any transaction
/// </summary>
public void CloseSharedConnection()
{
SqlTransConn transConn = transConnCollection["TransConn_" + connHashKey];
if (transConn != null)
{
transConn.Close();
transConnCollection.Remove("TransConn_" + connHashKey);
}
}
/// <summary>
/// Close and abort any transaction
/// </summary>
public static void CloseSharedConnection(string connectionName)
{
SqlTransConn transConn = DAGloble.TransactionPool.ThreadSafeRead <string, SqlTransConn>(connectionName, "TransactionPool");
if (transConn != null)
{
transConn.Close();
DAGloble.TransactionPool.ThreadSafeRemove <string, SqlTransConn>(connectionName, "TransactionPool");
}
}
public List <T> ExecuteProcAndGetOutput(Query query, out int outParamValue)
{
int v_iOutputIndex = -1;
List <SqlParameter> parameters = new List <SqlParameter>();
SqlTransConn transConn = GetConnection();
string storeProcName = query.Procedure;
int i = 0;
foreach (string key in query.Parameters.Keys)
{
SqlParameter parameter = new SqlParameter(key, query.Parameters[key].Type);
parameter.Value = query.Parameters[key].Value;
parameter.Direction = query.Parameters[key].Direction;
parameters.Add(parameter);
if (parameter.Direction == ParameterDirection.Output || parameter.Direction == ParameterDirection.ReturnValue)
{
v_iOutputIndex = i;
}
i++;
}
//List<T> result;
List <T> list = new List <T>();
try
{
using (SqlDataReader reader = SqlHelper.ExecuteReader(transConn, CommandType.StoredProcedure, storeProcName, parameters, false))
{
if (reader.HasRows)
{
while (reader.Read())
{
T to = new T();
to.Initialize(reader);
LateBinding(ref to, query);
list.Add(to);
}
}
}
//reader.Close();// Should close DataReader first so that can get parameters value.
outParamValue = int.Parse(parameters[v_iOutputIndex].Value.ToString());
//outParamValue = 1;
}
catch (Exception ex)
{
throw new Exception("Failed to execute store proc:" + storeProcName, ex);
}
finally
{
transConn.Close();
}
return(list);
}
private List <T> _findAllFromSQLText(string sql, List <SqlParameter> parameters)
{
List <T> list = new List <T>();
SqlTransConn transConn = GetConnection(_connectionName);
try
{
using (SqlCommand command = transConn.CreateCommand())
{
command.CommandText = sql;
command.CommandType = CommandType.Text;
command.CommandTimeout = 120;
if (null != parameters)
{
foreach (SqlParameter parm in parameters)
{
command.Parameters.Add(parm);
}
}
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
T to = new T();
to.Initialize(reader);
//LateBinding(ref to, query);
list.Add(to);
}
}
}
command.Parameters.Clear();
}
}
catch (Exception ex)
{
throw new Exception(sql + ",message:" + ex.Message, ex);
}
finally
{
if (!transConn.IsInTransaction())
{
transConn.Close();
}
}
return(list);
}
/// <summary>
/// return the value of the parameter which is sqldbDirection.outPut
/// </summary>
/// <param name="v_sStoreProcName"></param>
/// <param name="v_oSqlParams"></param>
/// <returns></returns>
public Q ExecuteProcAndGetOutput <Q>(Query query)
{
Q v_sReturnValue;
int v_iOutputIndex = -1;
List <SqlParameter> parameters = new List <SqlParameter>();
SqlTransConn transConn = GetConnection();
string storeProcName = query.Procedure;
// generic, no mapping
// rely on input parameter type
int i = 0;
foreach (string key in query.Parameters.Keys)
{
SqlParameter parameter = new SqlParameter(key, query.Parameters[key].Type);
parameter.Value = query.Parameters[key].Value;
parameter.Direction = query.Parameters[key].Direction;
parameters.Add(parameter);
if (parameter.Direction == ParameterDirection.Output || parameter.Direction == ParameterDirection.ReturnValue)
{
v_iOutputIndex = i;
}
i++;
}
try
{
SqlHelper.ExecuteNonQuery(transConn, CommandType.StoredProcedure, storeProcName, parameters);
if (v_iOutputIndex > -1)
{
v_sReturnValue = (Q)parameters[v_iOutputIndex].Value;
}
else
{
v_sReturnValue = default(Q);
}
}
catch (Exception ex)
{
throw new Exception("Failed to execute store proc:" + storeProcName, ex);
}
finally
{
transConn.Close();
}
return(v_sReturnValue);
}
private void _updateOrAddOrDeleteDBFromStoreProc(Query query)
{
List <SqlParameter> parameters = new List <SqlParameter>();
string storeProcName;
if (StoreProcMap != null && StoreProcMap.ContainsKey(query.Procedure))
{
Query.StoreProcType storeproc = this.StoreProcMap[query.Procedure];
storeProcName = storeproc.Name;
foreach (string key in storeproc.Parameters.Keys)
{
// use parameter type from storeproc definition
SqlParameter parameter = new SqlParameter(key, storeproc.Parameters[key]);
parameter.Value = query.Parameters[key].Value;
parameters.Add(parameter);
}
}
else
{
storeProcName = query.Procedure;
// generic, no mapping
// rely on input parameter type
foreach (string key in query.Parameters.Keys)
{
SqlParameter parameter = new SqlParameter(key, query.Parameters[key].Type);
parameter.Value = query.Parameters[key].Value;
parameters.Add(parameter);
}
}
SqlTransConn transConn = GetConnection();
try
{
SqlHelper.ExecuteNonQuery(transConn, CommandType.StoredProcedure, storeProcName, parameters);
}
catch (Exception ex)
{
throw new Exception("Failed to execute store proc:" + storeProcName, ex);
}
finally
{
transConn.Close();
}
}
public int DeleteByPKArray(long[] idArrar)
{
StringBuilder sb = new StringBuilder();
sb.Append("Delete from ").Append(_tableName);
string[] primaryKeys = BaseTO.GetSQLPrimaryKey(BaseTO.GetTableName <T>());
if (null == primaryKeys || 0 == primaryKeys.Length)
{
throw new ExceptionNoPrimaryKey();
}
if (1 < primaryKeys.Length)
{
//此方法只用于实体类只有一个主键的情况
throw new ExceptionErrorPrimaryKeyNumber();
}
string idList = string.Join(",", idArrar);
sb.Append(" where ")
.Append(primaryKeys[0])
.Append(" in (")
.Append(idList)
.Append(")");
List <SqlParameter> parameters = new List <SqlParameter>();
SqlTransConn transConn = GetConnection(_connectionName);
int result = 0;
try
{
result = SqlHelper.ExecuteNonQuery(transConn, CommandType.Text, sb.ToString(), null);
}
catch (Exception ex)
{
throw new Exception(ex.Message + ":" + sb.ToString(), ex);
}
finally
{
if (!transConn.IsInTransaction())
{
transConn.Close();
}
}
return(result);
}
public int UpdateFromTO(BaseTO to, object PKValue)
{
Dictionary <string, object> dict = to.GetSQLWriteValues();
StringBuilder sb = new StringBuilder();
sb.Append("update ").Append(TableName).Append(" set ");
List <SqlParameter> parameters = new List <SqlParameter>();
int i = 0;
foreach (string name in dict.Keys)
{
if (i > 0)
{
sb.Append(", ");
}
sb.Append(name).Append("=@").Append(name);
parameters.Add(Query.BuilderParam(name, dict[name]));
i++;
}
sb.Append(" where ").Append(PrimaryKey).Append("=@PKValue");
parameters.Add(new SqlParameter("@PKValue", PKValue));
SqlTransConn transConn = GetConnection();
int result = 0;
try
{
result = SqlHelper.ExecuteNonQuery(transConn, CommandType.Text, sb.ToString(), parameters);
}
catch (Exception ex)
{
throw new Exception(ex.Message + ":" + sb.ToString(), ex);
}
finally
{
transConn.Close();
}
return(result);
}
public int DeleteByPK(long id)
{
StringBuilder sb = new StringBuilder();
sb.Append("Delete from ").Append(_tableName);
string[] primaryKeys = BaseTO.GetSQLPrimaryKey(BaseTO.GetTableName <T>());
if (null == primaryKeys || 0 == primaryKeys.Length)
{
throw new ExceptionNoPrimaryKey();
}
if (1 < primaryKeys.Length)
{
//此方法只用于实体类只能有一个主键的情况
throw new ExceptionErrorPrimaryKeyNumber();
}
sb.Append(" where ").Append(primaryKeys[0]).Append("=@PKValue");
List <SqlParameter> parameters = new List <SqlParameter>();
SqlParameter parameter = new SqlParameter("@PKValue", SqlDbType.Int, 0);
parameter.Value = id;
parameters.Add(parameter);
SqlTransConn transConn = GetConnection(_connectionName);
int result = 0;
try
{
result = SqlHelper.ExecuteNonQuery(transConn, CommandType.Text, sb.ToString(), parameters);
}
catch (Exception ex)
{
throw new Exception(ex.Message + ":" + sb.ToString(), ex);
}
finally
{
if (!transConn.IsInTransaction())
{
transConn.Close();
}
}
return(result);
}
public int InsertFromTO(BaseTO to)
{
Dictionary <string, object> dict = to.GetSQLWriteValues();
StringBuilder sb = new StringBuilder();
sb.Append("Insert into ").Append(TableName).Append(" (");
sb.Append(String.Join(", ", dict.Keys.ToArray()));
sb.Append(") values (");
List <SqlParameter> parameters = new List <SqlParameter>();
int i = 0;
foreach (string name in dict.Keys)
{
if (i > 0)
{
sb.Append(", ");
}
sb.Append("@").Append(name);
parameters.Add(Query.BuilderParam(name, dict[name]));
i++;
}
sb.Append(")");
SqlTransConn transConn = GetConnection();
int result = 0;
try
{
result = SqlHelper.ExecuteNonQuery(transConn, CommandType.Text, sb.ToString(), parameters);
}
catch (Exception ex)
{
throw new Exception(ex.Message + ":" + sb.ToString(), ex);
}
finally
{
transConn.Close();
}
return(result);
}
public int DeleteByPK(T t)
{
StringBuilder sb = new StringBuilder();
sb.Append("Delete from ").Append(_tableName);
string[] primaryKeys = t.GetPrimaryKey <T>();
if (null == primaryKeys || 0 == primaryKeys.Length)
{
throw new ExceptionNoPrimaryKey();
}
sb.Append(" where 1=1");
List <SqlParameter> parameters = new List <SqlParameter>();
foreach (string pk in primaryKeys)
{
sb.Append(" and ").Append(pk).Append("=@" + pk);
SqlParameter parameter = new SqlParameter("@" + pk, SqlDbType.NVarChar);
parameter.Value = t.GetPropertyValue(pk);
parameters.Add(parameter);
}
SqlTransConn transConn = GetConnection(_connectionName);
int result = 0;
try
{
result = SqlHelper.ExecuteNonQuery(transConn, CommandType.Text, sb.ToString(), parameters);
}
catch (Exception ex)
{
throw new Exception(ex.Message + ":" + sb.ToString(), ex);
}
finally
{
if (!transConn.IsInTransaction())
{
transConn.Close();
}
}
return(result);
}
public List <T> ExecuteQueryStringAndReturn(string v_sQueryString, SqlParameter[] v_oSqlParams)
{
List <T> list = new List <T>();
SqlTransConn transConn = GetConnection();
try
{
using (SqlCommand command = transConn.CreateCommand())
{
command.CommandText = v_sQueryString;
command.CommandType = CommandType.Text;
foreach (SqlParameter parm in v_oSqlParams)
{
command.Parameters.Add(parm);
}
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
T to = new T();
to.Initialize(reader);
list.Add(to);
}
}
}
command.Parameters.Clear();
}
}
catch (Exception ex)
{
throw new Exception(v_sQueryString, ex);
}
finally
{
transConn.Close();
}
return(list);
}
public object ExecuteScalar(string sqlText, List <SqlParameter> parameters)
{
object result = null;
SqlTransConn transConn = GetConnection(_connectionName);
try
{
result = SqlHelper.ExecuteScalar(transConn, CommandType.Text, sqlText, parameters);
}
catch (Exception ex)
{
throw new Exception(ex.Message + ":" + sqlText, ex);
}
finally
{
if (!transConn.IsInTransaction())
{
transConn.Close();
}
}
return(result);
}
public int ExecuteQueryStringAndReturnId(string v_sQueryString, SqlParameter[] v_oSqlParams)
{
int nowid = 0;
DataSet ds = new DataSet();
SqlDataAdapter dap = new SqlDataAdapter();
SqlTransConn transConn = GetConnection();
try
{
SqlCommand command = transConn.CreateCommand();
command.CommandText = v_sQueryString;
command.CommandType = CommandType.Text;
foreach (SqlParameter parm in v_oSqlParams)
{
command.Parameters.Add(parm);
}
dap.SelectCommand = command;
dap.Fill(ds);
if (ds.Tables[0].Rows[0][1].ToString() != "0")
{
return(0);
}
else
{
nowid = Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString());
}
return(nowid);
}
catch (Exception ex)
{
throw new Exception(v_sQueryString, ex);
}
finally
{
transConn.Close();
}
}
public void ExecuteQueryString(string v_sQueryString, SqlParameter[] v_oSqlParams, string key)
{
SqlTransConn transConn = GetConnection();
if (!string.IsNullOrEmpty(key))
{
if (this.UpdateMap.ContainsKey(key))
{
v_sQueryString = this.UpdateMap[key];
}
}
try
{
using (SqlCommand command = transConn.CreateCommand())
{
command.CommandText = v_sQueryString;
command.CommandType = CommandType.Text;
foreach (SqlParameter parm in v_oSqlParams)
{
command.Parameters.Add(parm);
}
command.ExecuteNonQuery();
command.Parameters.Clear();
}
}
catch (Exception ex)
{
throw new Exception(v_sQueryString, ex);
}
finally
{
transConn.Close();
}
}
/// <summary>
/// Insert a entity to database.
/// </summary>
/// <param name="to">Entity object</param>
/// <returns>If entity's primary key is identify field return the new id, else return the return value of SqlCommand.ExecuteNonQuery()</returns>
public object InsertFromTO(BaseTO to)
{
Dictionary <string, object> dict = to.GetSQLWriteValues();
//我把recordcount放在baseTo里面，用于列表分页时返回记录总数，这里新增修改时需要过滤掉 kevin
dict.Remove("recordcount");
bool hasIdentifyPK = false;
string[] primaryKay = to.GetPrimaryKey <T>();
foreach (string keyName in primaryKay)
{
SQLFieldAttribute[] attrs = to.GetFieldPropertyAttribute <SQLFieldAttribute>(keyName);
if (null != attrs)
{
foreach (SQLFieldAttribute a in attrs)
{
hasIdentifyPK = a.IsIdentify;
break;
}
}
if (hasIdentifyPK)
{
break;
}
}
StringBuilder sb = new StringBuilder();
sb.Append("Insert into ").Append(_tableName).Append(" (");
sb.Append(String.Join(", ", dict.Keys.ToArray()));
sb.Append(") values (");
List <SqlParameter> parameters = new List <SqlParameter>();
int i = 0;
foreach (string name in dict.Keys)
{
if (i > 0)
{
sb.Append(", ");
}
sb.Append("@").Append(name);
parameters.Add(Query.BuilderParam(name, dict[name]));
i++;
}
sb.Append(")");
if (0 == to.CustomPrimaryKeyIdentify ? hasIdentifyPK : (1 == to.CustomPrimaryKeyIdentify))
{
sb.Append(";select @@identity");
}
SqlTransConn transConn = GetConnection(_connectionName);
object result = 0;
try
{
if (0 == to.CustomPrimaryKeyIdentify ? hasIdentifyPK : (1 == to.CustomPrimaryKeyIdentify))
{
result = SqlHelper.ExecuteScalar(transConn, CommandType.Text, sb.ToString(), parameters);
}
else
{
result = SqlHelper.ExecuteNonQuery(transConn, CommandType.Text, sb.ToString(), parameters);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message + ":" + sb.ToString(), ex);
}
finally
{
if (!transConn.IsInTransaction())
{
transConn.Close();
}
}
return(result);
}
/// <summary>
/// Get the count(*) of the SQL built from the filter, without the limit/paging
/// </summary>
/// <param name="filter"></param>
/// <returns></returns>
public virtual int GetTotal(Query query)
{
SetInternalBinding(true);
int total = 0;
SqlBuilder builder;
if (query != null)
{
try
{
builder = InitSqlBuilder(query);
}
catch (ExceptionEmptyQuery)
{
return(0);
}
catch (ExceptionBadFilterSequence)
{
// return empty list
return(0);
}
}
else
{
throw new Exception("Not supported");
}
string sql = builder.BuildCountSql();
SqlTransConn transConn = GetConnection(_connectionName);
try
{
using (SqlCommand command = transConn.CreateCommand())
{
command.CommandText = sql;
command.CommandType = CommandType.Text;
List <SqlParameter> parameters = builder.GetSqlParams();
foreach (SqlParameter param in parameters)
{
command.Parameters.Add(param);
}
total = (int)command.ExecuteScalar();
command.Parameters.Clear();
}
}
catch (Exception ex)
{
throw new Exception(sql, ex);
}
finally
{
if (!transConn.IsInTransaction())
{
transConn.Close();
}
}
// set this so that the value can be used to determine IsValidRange of limit/offset/page
query.Total = total;
return(total);
}
private List <T> _findAllFromStoreProc(Query query)
{
List <T> list = new List <T>();
List <SqlParameter> parameters = new List <SqlParameter>();
string storeProcName;
if (StoreProcMap != null && StoreProcMap.ContainsKey(query.Procedure))
{
Query.StoreProcType storeproc = this.StoreProcMap[query.Procedure];
storeProcName = storeproc.Name;
foreach (string key in storeproc.Parameters.Keys)
{
// use parameter type from storeproc definition
SqlParameter parameter = new SqlParameter(key, storeproc.Parameters[key]);
parameter.Value = query.Parameters[key].Value;
parameters.Add(parameter);
}
}
else
{
storeProcName = query.Procedure;
// generic, no mapping
// rely on input parameter type
foreach (string key in query.Parameters.Keys)
{
SqlParameter parameter = new SqlParameter(key, query.Parameters[key].Type);
parameter.Value = query.Parameters[key].Value;
parameters.Add(parameter);
}
}
SqlTransConn transConn = GetConnection(_connectionName);
try
{
using (SqlDataReader reader = SqlHelper.ExecuteReader(transConn, CommandType.StoredProcedure, storeProcName, parameters))
{
if (reader.HasRows)
{
while (reader.Read())
{
T to = new T();
to.Initialize(reader);
LateBinding(ref to, query);
list.Add(to);
}
}
}
}
catch (Exception ex)
{
throw new Exception("Failed to execute store proc:" + storeProcName, ex);
}
finally
{
if (!transConn.IsInTransaction())
{
transConn.Close();
}
}
return(list);
}