public ExecuteResult ExecuteNonQuery(string commandText, List <SqlDBParameter> parameters = null, CommandType commandType = CommandType.StoredProcedure, bool existDataSet = false)
{
var result = new ExecuteResult();
if (_connection == null)
{
_connection = CreateConnection();
if (!_IsOpened)
{
_connection.Open();
_IsOpened = true;
}
}
try
{
//if (parameters != null && parameters.Count > 0)
//{
// foreach (var param in parameters)
// {
// if (param.Value.GetType().Name.ToLower() == "string")
// {
// string name = "@" + param.ParameterName;
// commandText = commandText.Replace(name, "+N''+ " + name);
// }
// }
//}
var command = new SqlCommand
{
Connection = _connection,
CommandText = commandText,
CommandType = commandType,
CommandTimeout = 0
};
if (parameters != null && parameters.Count > 0)
{
foreach (var param in parameters)
{
var parameter = new SqlParameter();
if (param.DBType != SqlDBType.None)
{
parameter.SqlDbType = (SqlDbType)Enum.Parse(typeof(SqlDbType), param.DBType.ToString());
}
parameter.Direction = param.Direction;
parameter.ParameterName = param.ParameterName;
parameter.Size = param.Size;
if (string.IsNullOrEmpty(Convert.ToString(param.Value)))
{
parameter.Value = DBNull.Value;
}
else
{
parameter.Value = param.Value;
}
command.Parameters.Add(parameter);
}
}
if (_IsTransaction)
{
command.Transaction = _Transaction;
}
var status = command.ExecuteNonQuery();
if (existDataSet)
{
result.OutputDataSet = new DataSet();
using (var dataAdapter = new SqlDataAdapter())
{
dataAdapter.SelectCommand = command;
dataAdapter.Fill(result.OutputDataSet);
}
}
if (parameters != null && parameters.Count > 0)
{
var output = parameters.Where(m => m.Direction == ParameterDirection.InputOutput || m.Direction == ParameterDirection.Output);
if (output.Any())
{
result.OutputData = new Dictionary <string, string>();
foreach (var item in output)
{
result.OutputData.Add(item.ParameterName, Convert.ToString(command.Parameters[item.ParameterName].Value));
}
}
}
result.data = status;
result.Status = status != -1;
}
catch (Exception ex)
{
result.Status = false;
result.ErrorMessage = ex.Message;
//log.Error(Environment.NewLine + "ExecuteNonQuery:" + commandText, ex);
//throw ex;
}
finally
{
if (!_IsTransaction)
{
_connection.Close();
_connection.Dispose();
_connection = null;
_IsOpened = false;
}
}
return(result);
}
public ExecuteResult ExecuteCmdDataSetPaging(string commandText, string orderby, int start, int length, List <SqlDBParameter> parameters = null)
{
var result = new ExecuteResult();
if (_connection == null)
{
_connection = CreateConnection();
}
try
{
if (!_IsOpened)
{
_connection.Open();
_IsOpened = true;
}
var command = new SqlCommand
{
Connection = _connection,
CommandType = CommandType.Text,
CommandTimeout = 0
};
if (_IsTransaction && _Transaction != null)
{
command.Transaction = _Transaction;
}
if (parameters != null && parameters.Count > 0)
{
foreach (var param in parameters)
{
var parameter = new SqlParameter();
if (param.DBType != SqlDBType.None)
{
parameter.SqlDbType = (SqlDbType)Enum.Parse(typeof(SqlDbType), param.DBType.ToString());
}
parameter.Direction = param.Direction;
parameter.ParameterName = param.ParameterName;
parameter.Size = param.Size;
if (string.IsNullOrEmpty(Convert.ToString(param.Value)))
{
parameter.Value = DBNull.Value;
}
else
{
parameter.Value = param.Value;
}
command.Parameters.Add(parameter);
}
}
command.CommandText = "select count(*) from (" + commandText + ") a";
result.data = command.ExecuteScalar();
command.CommandText = commandText + " order by " + orderby + " OFFSET " + start + " rows FETCH NEXT " + length + " ROWS ONLY";
result.OutputDataSet = new DataSet();
using (var dataAdapter = new SqlDataAdapter())
{
dataAdapter.SelectCommand = command;
dataAdapter.Fill(result.OutputDataSet);
}
#region Change
//command.CommandText = commandText + " order by " + orderby + " OFFSET " + start + " rows FETCH NEXT " + (length + 1) + " ROWS ONLY";
//var dataSet = new DataSet();
//using (var dataAdapter = new SqlDataAdapter())
//{
// dataAdapter.SelectCommand = command;
// dataAdapter.Fill(dataSet);
//}
//result.data = dataSet.Tables[0].Rows.Count;
//if (dataSet.Tables[0].Rows.Count > length)
//{
// dataSet.Tables[0].Rows[length].Delete();
//}
//result.OutputDataSet = dataSet;
#endregion
var output = parameters.Where(m => m.Direction == ParameterDirection.InputOutput || m.Direction == ParameterDirection.Output);
if (output.Any())
{
result.OutputData = new Dictionary <string, string>();
foreach (var item in output)
{
result.OutputData.Add(item.ParameterName, Convert.ToString(command.Parameters[item.ParameterName].Value));
}
}
result.Status = true;
}
catch (Exception ex)
{
result.Status = false;
result.ErrorMessage = ex.Message;
//log.Error(Environment.NewLine + "ExecuteDataSet " + commandText, ex);
//throw ex;
}
finally
{
if (_Transaction == null)
{
_connection.Close();
_connection.Dispose();
_connection = null;
_IsOpened = false;
}
}
return(result);
}
public ExecuteResult ExecuteScalar(string commandText, List <SqlDBParameter> parameters = null, CommandType commandType = CommandType.Text)
{
var result = new ExecuteResult();
if (_connection == null)
{
_connection = CreateConnection();
}
try
{
if (!_IsOpened)
{
_connection.Open();
_IsOpened = true;
}
var command = new SqlCommand
{
Connection = _connection,
CommandText = commandText,
CommandType = commandType,
CommandTimeout = 0
};
if (_IsTransaction && _Transaction != null)
{
command.Transaction = _Transaction;
}
if (parameters != null && parameters.Count > 0)
{
foreach (var param in parameters)
{
var parameter = new SqlParameter();
if (param.DBType != SqlDBType.None)
{
parameter.SqlDbType = (SqlDbType)Enum.Parse(typeof(SqlDbType), param.DBType.ToString());
}
parameter.Direction = param.Direction;
parameter.ParameterName = param.ParameterName;
parameter.Size = param.Size;
if (string.IsNullOrEmpty(Convert.ToString(param.Value)))
{
parameter.Value = DBNull.Value;
}
else
{
parameter.Value = param.Value;
}
command.Parameters.Add(parameter);
}
}
result.data = command.ExecuteScalar();
result.Status = true;
}
catch (Exception ex)
{
result.Status = false;
result.ErrorMessage = ex.Message;
//log.Error(Environment.NewLine + "ExecuteDataSet " + commandText, ex);
//throw ex;
}
finally
{
if (_Transaction == null)
{
_connection.Close();
_connection.Dispose();
_connection = null;
_IsOpened = false;
}
}
return(result);
}