/// <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();
}
}
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);
}
// 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();
}
}
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);
}
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);
}
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 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>
/// 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);
}
