public SQLResults ExecuteQuery(string sqlCmd)
{
try
{
SQLResults results = null;
using (Connection = new SqlConnection(_connectionString))
{
using (var cmd = new SqlCommand())
{
var dt = new DataTable();
if (Connection.State != ConnectionState.Open)
{
Connection.Open();
}
cmd.Connection = Connection;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = sqlCmd;
cmd.AddSqlParameters(cmd);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
if (HasSPFailed(cmd))
{
results = new SQLResults(
cmd.Parameters["@errorMessage"].Value.ToString(),
cmd.Parameters["@errorNumber"].Value.ToString(),
cmd.Parameters["@errorSeverity"].Value.ToString(),
cmd.Parameters["@errorState"].Value.ToString(),
cmd.Parameters["@errorProcedure"].Value.ToString(),
cmd.Parameters["@errorLine"].Value.ToString(),
cmd.CommandText
);
}
else
{
results = new SQLResults(dt);
}
}
}
return(results);
}
catch (Exception)
{
throw;
}
}
public SQLResults ExecuteNonQuery(string sqlCmd, List <SqlParameter> paramList)
{
try
{
SQLResults results = null;
using (Connection = new SqlConnection(_connectionString))
{
using (var cmd = new SqlCommand())
{
var dt = new DataTable();
Connection.Open();
cmd.Connection = Connection;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = sqlCmd;
cmd.AddSqlParameters(cmd);
cmd.AddRequiredSqlParameters(cmd, paramList);
var resultCount = cmd.ExecuteNonQuery();
if (HasSPFailed(cmd) && resultCount > 0)
{
results = new SQLResults(
cmd.Parameters["@errorMessage"].Value.ToString(),
cmd.Parameters["@errorNumber"].Value.ToString(),
cmd.Parameters["@errorSeverity"].Value.ToString(),
cmd.Parameters["@errorState"].Value.ToString(),
cmd.Parameters["@errorProcedure"].Value.ToString(),
cmd.Parameters["@errorLine"].Value.ToString(),
cmd.CommandText
);
}
else
{
results = new SQLResults(resultCount);
}
}
}
return(results);
}
catch (Exception)
{
throw;
}
}