public void UpdateValue(TablesExam table, int id, params FieldValue[] fieldValue)
{
string command = string.Format("UPDATE {0} SET ", table.ToStringCorrect());
bool first = true;
foreach (var item in fieldValue)
{
if (first)
{
first = false;
}
else
{
command += ", ";
}
command += string.Format("{0}='{1}' ", item.Field, item.Value);
}
command += "WHERE id=" + id;
SqlCommand cmd = new SqlCommand(command, connection);
cmd.ExecuteNonQuery();
}
public void DeleteValue(TablesExam table, params FieldValue[] fieldValue)
{
int id = GetID(table, fieldValue);
string command = string.Format("DELETE FROM [{0}] WHERE ", table.ToStringCorrect());
bool first = true;
foreach (var item in fieldValue)
{
if (first)
{
first = false;
}
else
{
command += " AND ";
}
command += string.Format("{0}='{1}' ", item.Field, item.Value);
}
SqlCommand cmd = new SqlCommand(command, connection);
cmd.ExecuteNonQuery();
}
int InsertValue(TablesExam table, bool WithoutReturnID, params string[] value)
{
string command = string.Format("INSERT INTO [{0}] VALUES (", table.ToStringCorrect()); //==TablesExam.Question_Answer? "Question-Answer":table==TablesExam.Ticket_Question? table.ToString()) );//[Exam].[dbo].[
foreach (var item in value)
{
if (WithoutReturnID)
{
command += "'" + item + "',";
}
else
{
if (item == null)
{
command += "null,"; continue;
}
command += "'" + PrepareString(item) + "',";
}
}
command = command.TrimEnd(',');
if (WithoutReturnID)
{
command += "); ";
}
else
{
command += string.Format("); SELECT id FROM [{0}] WHERE id =@@IDENTITY; ", table);
}
SqlCommand cmd = connection.CreateCommand();
cmd.CommandText = command;
cmd.Transaction = connection.BeginTransaction();
try
{
if (WithoutReturnID)
{
cmd.ExecuteNonQuery();
cmd.Transaction.Commit();
return(0);
}
else
{
int rez = (int)cmd.ExecuteScalar();
cmd.Transaction.Commit();
return(rez);
}
}
catch (Exception ex)
{
try { cmd.Transaction.Rollback(); throw new Exception(ex.Message); } catch (Exception) { throw new Exception("Rollback InsertValue didnt work: " + ex.Message); }
}
}
int GetID(TablesExam table, params FieldValue[] fieldvalue)
{
int rez = -1;
var ob = GetField("id", table, fieldvalue);
if (ob != null)
{
int.TryParse(ob.ToString(), out rez);
}
return(rez);
}
IDictionary <int, string> GetAllOneFields(TablesExam table)
{
Dictionary <int, string> output = new Dictionary <int, string>();
SqlCommand cmd = new SqlCommand(string.Format("SELECT * FROM [{0}]", table), connection);
using (var reader = cmd.ExecuteReader())
while (reader.Read())
{
output.Add(reader.GetInt32(0), reader.GetString(1));
}
return(output);
}
int InsertName(TablesExam table, string nameField, string nameValue)
{
if (nameField != null)
{
var id = GetID(table, new FieldValue(nameField, nameValue));
if (id > 0)
{
return(id);
}
}
return(InsertValue(table, false, nameValue));
}
public static string ToStringCorrect(this TablesExam table)
{
switch (table)
{
case TablesExam.Question_Answer:
return("Question-Answer");
case TablesExam.Ticket_Question:
return("Ticket-Question");
default:
return(table.ToString());
}
}
object GetField(string fieldNameGet, TablesExam table, params FieldValue[] fieldvalue)
{
string command = string.Format("SELECT {0} FROM [{1}] WHERE ", fieldNameGet, table);
bool first = true;
foreach (var item in fieldvalue)
{
if (first)
{
first = false;
}
else
{
command += " AND ";
}
command += string.Format("{0}='{1}' ", item.Field, item.Value);
}
SqlCommand cmd = new SqlCommand(command, connection);
return(cmd.ExecuteScalar());
}
