public MessageDataBase DeleteCommand(string sql)
{
SqlCommand command = new SqlCommand();
MessageDataBase result = new MessageDataBase();
result.Check = false;
command.Connection = connection;
command.CommandType = CommandType.Text;
command.CommandText = sql;
try
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
result.Result = command.ExecuteNonQuery();
connection.Close();
result.Check = true;
}
catch (Exception ex)
{
result.ErrorMessage = ex.Message;
result.Result = 0;
result.Check = false;
}
finally
{
}
return(result);
}
public MessageDataBase RunStoreProcedureWithoutParameters(string storeName)
{
SqlCommand command = new SqlCommand();
command.Connection = connection;
command.CommandType = CommandType.StoredProcedure;
MessageDataBase result = new MessageDataBase();
command.CommandText = storeName;
try
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
result.Result = command.ExecuteNonQuery();
connection.Close();
result.Check = true;
}
catch (Exception ex)
{
result.ErrorMessage = ex.Message;
result.Result = 0;
result.Check = false;
}
finally
{
}
return(result);
}
public MessageDataBase RunStoreProcedure(string storeName, List <SqlParameter> parameters)
{
SqlCommand command = new SqlCommand();
command.Connection = connection;
command.CommandType = CommandType.StoredProcedure;
MessageDataBase result = new MessageDataBase();
command.CommandText = storeName;
if (parameters != null)
{
command.Parameters.AddRange(parameters.ToArray());
}
try
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
result.Result = Convert.ToInt32(command.ExecuteScalar().ToString());
connection.Close();
result.Check = true;
}
catch (Exception ex)
{
result.ErrorMessage = ex.Message;
result.Result = 0;
result.Check = false;
}
finally
{
}
return(result);
}
public MessageDataBase InsertAndGetLastID(string sql)
{
MessageDataBase LastID = new MessageDataBase();
LastID.Result = -1;
SqlCommand command = new SqlCommand();
try
{
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
command.Connection = connection;
command.CommandType = CommandType.Text;
command.CommandText = sql + ";SELECT @@IDENTITY;";
LastID.Result = Convert.ToInt32(command.ExecuteScalar());
connection.Close();
}
catch (Exception er)
{
LastID.ErrorMessage = er.Message;
LastID.Result = -1;
}
return(LastID);
}
public MessageDataBase FillCombo(ComboBox combo, string tablename, string fieldname, string id)
{
string sql = string.Format("select {0},{1} from {2}", id, fieldname, tablename);
MessageDataBase message = new MessageDataBase();
DataSet data = SelectCommnad(sql);
if (data.Tables.Count > 0)
{
combo.DataSource = data.Tables[0];
combo.DisplayMember = fieldname;
combo.ValueMember = id;
message.Check = true;
message.Result = data.Tables[0].Rows.Count;
}
else
{
message.Check = false;
message.ErrorMessage = " جدولی انتخاب نشده است";
}
return(message);
}
public MessageDataBase TDeleteCommand(string[] sql)
{
MessageDataBase result = new MessageDataBase();
result.Check = false;
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
SqlTransaction transaction = connection.BeginTransaction("DeleteTransaction");
SqlCommand command = connection.CreateCommand();
command.Connection = connection;
command.CommandType = CommandType.Text;
command.Transaction = transaction;
try
{
for (int i = 0; i < sql.Length; i++)
{
command.CommandText = sql[i];
result.Result = command.ExecuteNonQuery();
}
}
catch (Exception ex)
{
transaction.Rollback();
result.Check = false;
result.Result = 0;
result.ErrorMessage = ex.Message;
}
finally
{
transaction.Commit();
connection.Close();
result.Check = true;
}
return(result);
}