public DataTable SelectCommand(string tableName, List <string> selected, DBParams whereParams)
{
DataTable dataTable = new DataTable();
SqlConnection connection = new SqlConnection(this.conString);
try
{
connection.Open();
SqlCommand command = connection.CreateCommand();
string str = "select ";
using (List <string> .Enumerator enumerator = selected.GetEnumerator())
{
while (enumerator.MoveNext())
{
string current = enumerator.Current;
str = str + ",";
}
}
str = str.Substring(0, str.Length - 1) + " where ";
foreach (DBParam param in whereParams.GetParams())
{
string str2 = str;
str = str2 + param.Name + " = '" + param.Value.ToString() + "' and ";
}
str = str.Substring(0, str.Length - 5);
command.CommandText = str;
command.CommandType = CommandType.Text;
new SqlDataAdapter {
SelectCommand = command
}.Fill(dataTable);
}
catch (Exception exception)
{
MyUtilities.WriteToLogFile("SelectCommand : " + exception.Message);
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
return(dataTable);
}
/// <summary>
///
/// </summary>
/// <param name="url"></param>
/// <param name="post"></param>
/// <returns></returns>
public DataTable SelectProcedure(string procedureName, DBParams @params)
{
DataTable dataTable = new DataTable();
SqlConnection connection = new SqlConnection(this.conString);
try
{
connection.Open();
SqlCommand command = connection.CreateCommand();
command.CommandText = procedureName;
command.CommandType = CommandType.StoredProcedure;
foreach (DBParam param in @params.GetParams())
{
SqlParameter parameter = new SqlParameter(param.Name, param.Value);
if (param.HasType())
{
parameter.DbType = param.MyType;
}
if (param.HasSize())
{
parameter.Size = param.Size;
}
command.Parameters.Add(parameter);
}
new SqlDataAdapter {
SelectCommand = command
}.Fill(dataTable);
}
catch (Exception exception)
{
MyUtilities.WriteToLogFile("SelectProcedure : " + exception.Message);
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
return(dataTable);
}
/// <summary>
///
/// </summary>
/// <param name="url"></param>
/// <param name="post"></param>
/// <returns></returns>
public DataTable SelectCommand(string tableName, string[] selected, DBParams whereParams)
{
DataTable dataTable = new DataTable();
SqlConnection connection = new SqlConnection(this.conString);
try
{
connection.Open();
SqlCommand command = connection.CreateCommand();
string str = "select ";
foreach (string str2 in selected)
{
str = str + str2 + ",";
}
str = str.Substring(0, str.Length - 1) + " from " + tableName + " where ";
foreach (DBParam param in whereParams.GetParams())
{
string str3 = str;
str = str3 + param.Name + " = '" + param.Value.ToString() + "' and ";
}
str = str.Substring(0, str.Length - 5);
command.CommandText = str;
command.CommandType = CommandType.Text;
new SqlDataAdapter {
SelectCommand = command
}.Fill(dataTable);
}
catch (Exception exception)
{
MyUtilities.WriteToLogFile("SelectCommand : " + exception.Message);
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
return(dataTable);
}
public int UpdateCommand(string tableName, DBParams updated, DBParams whereParams)
{
int num = -1;
SqlConnection connection = new SqlConnection(this.conString);
try
{
connection.Open();
SqlCommand command = connection.CreateCommand();
string str = "update " + tableName + " set ";
foreach (DBParam param in updated.GetParams())
{
string str2 = str;
str = str2 + " " + param.Name + "='" + param.Value.ToString() + "',";
}
str = str.Substring(0, str.Length - 1) + " where ";
foreach (DBParam param2 in whereParams.GetParams())
{
object obj2 = str;
str = string.Concat(new object[] { obj2, param2.Name, " = '", param2.Value, "' and " });
}
str = str.Substring(0, str.Length - 5);
command.CommandText = str;
command.CommandType = CommandType.Text;
num = command.ExecuteNonQuery();
}
catch (Exception exception)
{
MyUtilities.WriteToLogFile("UpdateCommand : " + exception.Message);
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
return(num);
}
/// <summary>
///
/// </summary>
/// <param name="url"></param>
/// <param name="post"></param>
/// <returns></returns>
public object SelectScalarProcedure(string procedureName, DBParams @params)
{
object obj2 = new object();
SqlConnection connection = new SqlConnection(this.conString);
try
{
connection.Open();
SqlCommand command = connection.CreateCommand();
command.CommandText = procedureName;
command.CommandType = CommandType.StoredProcedure;
foreach (DBParam param in @params.GetParams())
{
SqlParameter parameter = new SqlParameter(param.Name, param.Value);
if (param.HasType())
{
parameter.DbType = param.MyType;
}
if (param.HasSize())
{
parameter.Size = param.Size;
}
command.Parameters.Add(parameter);
}
obj2 = command.ExecuteScalar();
}
catch (Exception exception)
{
MyUtilities.WriteToLogFile("SelectScalarProcedure : " + exception.Message);
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
return(obj2);
}
/// <summary>
///
/// </summary>
/// <param name="url"></param>
/// <param name="post"></param>
/// <returns></returns>
public int InsertUpdateDeleteProcedure(string procedureName, DBParams @params)
{
SqlConnection connection = new SqlConnection(this.conString);
int num = -1;
try
{
connection.Open();
SqlCommand command = connection.CreateCommand();
command.CommandText = procedureName;
command.CommandType = CommandType.StoredProcedure;
foreach (DBParam param in @params.GetParams())
{
SqlParameter parameter = new SqlParameter(param.Name, param.Value);
if (param.HasType())
{
parameter.DbType = param.MyType;
}
if (param.HasSize())
{
parameter.Size = param.Size;
}
command.Parameters.Add(parameter);
}
num = command.ExecuteNonQuery();
}
catch (Exception exception)
{
MyUtilities.WriteToLogFile("InsertUpdateProcedure : " + exception.Message);
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
return(num);
}
public int InsertCommand(string tableName, DBParams inserted)
{
int num = -1;
SqlConnection connection = new SqlConnection(this.conString);
try
{
connection.Open();
SqlCommand command = connection.CreateCommand();
string str = "insert into " + tableName + " ";
string str2 = "";
string str3 = "";
foreach (DBParam param in inserted.GetParams())
{
str2 = str2 + param.Name + ",";
str3 = str3 + "'" + param.Value.ToString() + "',";
}
str2 = "(" + str2.Substring(0, str2.Length - 1) + ")";
str3 = "(" + str3.Substring(0, str3.Length - 1) + ")";
str = str + str2 + " values " + str3;
command.CommandText = str;
command.CommandType = CommandType.Text;
num = command.ExecuteNonQuery();
}
catch (Exception exception)
{
MyUtilities.WriteToLogFile("InsertCommand : " + exception.Message);
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
return(num);
}
// Methods
static DBParams()
{
Empty = new DBParams();
}