private async void NewClick(object sender, EventArgs e)
{
string connectString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Request.PhysicalPath.Substring(0, Request.PhysicalPath.LastIndexOf('\\')) + @"\tol_db.mdb";
OleDbConnection SqlConnection = new OleDbConnection(connectString);
await SqlConnection.OpenAsync();
OleDbCommand command = new OleDbCommand("UPDATE [Курсы] SET Наименование = @panel1 , Направление = @panel2 , Продолжительность = @panel3 , Информация = @panel4 , Сложность = @panel5 WHERE Код = @id", SqlConnection);
command.Parameters.AddWithValue("@panel1", TextBox1.Text);
command.Parameters.AddWithValue("@panel2", TextBox2.Text);
command.Parameters.AddWithValue("@panel3", TextBox3.Text);
command.Parameters.AddWithValue("@panel4", TextBox4.Text);
command.Parameters.AddWithValue("@panel5", TextBox5.Text);
command.Parameters.AddWithValue("@id", Convert.ToInt32(Request.QueryString["kurseID"]));
await command.ExecuteNonQueryAsync();
int i = 0;
try
{
i = await command.ExecuteNonQueryAsync();
//Response.Write("<script>alert('" + i+ TextBox1.Text + "');</script>");
}
catch (Exception ex)
{
//Response.Write("<script>alert('"+ex.Message.ToString()+"..."+ex.Source.ToString()+"');</script>");
}
SqlConnection.Close();
}
private async void button6_Click(object sender, EventArgs e)
{
if (comboBox6.Text != "")
{
SqlConnection = new OleDbConnection(connectString);
await SqlConnection.OpenAsync();
OleDbDataReader sqlReader = null;
OleDbCommand command = new OleDbCommand("UPDATE [Клиенты] SET [Статус]=@status WHERE [Телефон]=@num", SqlConnection);
command.Parameters.AddWithValue("status", comboBox6.Text);
command.Parameters.AddWithValue("num", comboBox5.Text);
await command.ExecuteNonQueryAsync();
await command.ExecuteNonQueryAsync();
MessageBox.Show("Статус вопроса успешно изменен");
admin_panel open_form = new admin_panel();
open_form.Text = this.Text;
open_form.Show();
this.Hide();
}
else
{
comboBox6.BackColor = Color.Peru;
}
}
private async void Button_Copy16_Click_2_33(object sender, RoutedEventArgs e)
{
SqlConnection = new OleDbConnection(connectString);
await SqlConnection.OpenAsync();
OleDbDataReader sqlReader = null;
OleDbCommand command = new OleDbCommand("DELETE * FROM [Услуги] WHERE [Наименование]=@7", SqlConnection);
command.Parameters.AddWithValue("7", ComboBox.Text);
if (ComboBox.Text != "")
{
await command.ExecuteNonQueryAsync();
await command.ExecuteNonQueryAsync();
await command.ExecuteNonQueryAsync();
MessageBox.Show("Данные успешно удалены");
ComboBox.Text = "";
return;
}
ComboBox.Background = Brushes.Red;
ComboBox.Opacity = 0.4;
}
protected async void Button1_Click(object sender, EventArgs e)
{
SqlConnection = new OleDbConnection(connectString);
await SqlConnection.OpenAsync();
OleDbCommand command = new OleDbCommand("DELETE * FROM [Курсы учеников] WHERE [Код ученика]=@1 AND [Код курса]=@2", SqlConnection);
command.Parameters.AddWithValue("@1", Request.QueryString["userID"]);
command.Parameters.AddWithValue("@2", Request.QueryString["kurseID"]);
await command.ExecuteNonQueryAsync();
await command.ExecuteNonQueryAsync();
Response.Redirect("~/User_kurse_past.aspx?userID=" + Request.QueryString["userID"] + "&kurseID=" + Request.QueryString["kurseID"], false);
}
async Task <object> ExecuteNonQuery(string connectionString, string commandString)
{
OleDbConnection connection = null;
try
{
using (connection = new OleDbConnection(connectionString))
{
await connection.OpenAsync();
using (var command = new OleDbCommand(commandString, connection))
{
return(await command.ExecuteNonQueryAsync());
}
}
}
catch (Exception e)
{
throw new Exception("ExecuteNonQuery Error", e);
}
finally
{
connection.Close();
}
}
public async Task CreateTableIfNotExists()
{
using (OleDbConnection connection = new OleDbConnection(Settings.ConnectionString))
{
await connection.OpenAsync();
using (OleDbCommand command = connection.CreateCommand())
{
try
{
StringBuilder query = new StringBuilder();
query.Append($" create table {Settings.DatabaseTableToStoreLog} ");
query.Append($" ( ");
query.Append($" \"{"id".ToConventionPattern()}\" number(19) generated always as identity ");
query.Append($" ,\"{"system".ToConventionPattern()}\" varchar(50) ");
query.Append($" ,\"{"module".ToConventionPattern()}\" varchar(50) ");
query.Append($" ,\"{"version".ToConventionPattern()}\" varchar(50) ");
query.Append($" ,\"{"user".ToConventionPattern()}\" varchar(50) ");
query.Append($" ,\"{"date".ToConventionPattern()}\" timestamp ");
query.Append($" ,\"{"info".ToConventionPattern()}\" clob ");
query.Append($" ,\"{"info_description".ToConventionPattern()}\" varchar(100) ");
query.Append($" ,\"{"level".ToConventionPattern()}\" varchar(50) ");
query.Append($" );");
command.CommandText = query.ToString();
await command.ExecuteNonQueryAsync();
_tableCreated = true;
}
catch { _tableCreated = true; /* If table already exists will be caught here */ }
}
}
}
private async void btnDelete_Click(object sender, EventArgs e)
{
OleDbConnection connection;
InitializeDb(out connection);
OleDbCommand command = new OleDbCommand();
command.Connection = connection;
command.CommandType = CommandType.Text;
command.CommandText = "delete * from Employee where [ID] = @id";
command.Parameters.AddWithValue("@id", Convert.ToInt32(txtId.Text));
//command.Parameters.AddWithValue("@age", Convert.ToInt32(txtAge.Text));
//command.Parameters.AddWithValue("@salary", Convert.ToDecimal(txtSalary.Text));
//command.Parameters.AddWithValue("@company", txtCompany.Text);
try
{
connection.Open();
int rowsDeleted = await command.ExecuteNonQueryAsync();
Console.WriteLine("Rows Updated - {0}", rowsDeleted);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
connection.Close();
connection.Dispose();
}
ShowTableValues();
}
public async Task <int> CmdAsync(string cmd, params OleDbParameter[] parametros)
{
OleDbCommand Command = new OleDbCommand();
try
{
using (OleDbConnection conn = OpenConn())
{
Command.Connection = conn;
Command.CommandType = CommandType.Text;
Command.CommandText = cmd;
foreach (OleDbParameter parametro in parametros)
{
Command.Parameters.Add(parametro);
}
return(await Command.ExecuteNonQueryAsync());
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
Command.Dispose();
}
}
public async void about_click(object sender, EventArgs e)
{
string fn = System.IO.Path.GetFileName(File1.PostedFile.FileName);
string SaveLocation = Server.MapPath("Ready") + "\\" + fn;
try
{
File1.PostedFile.SaveAs(SaveLocation);
}
catch (Exception ex)
{
Response.Write("Error: " + ex.Message);
}
SqlConnection = new OleDbConnection(connectString);
await SqlConnection.OpenAsync();
OleDbDataReader sqlReader = null;
OleDbCommand command = new OleDbCommand("INSERT INTO [Задания учеников] ([Код ученика],[Код задания],[Наименование файла],[Код курса])VALUES(@1,@2,@3,@4)", SqlConnection);
command.Parameters.AddWithValue("@1", Request.QueryString["userID"]);
command.Parameters.AddWithValue("@2", Request.QueryString["queID"]);
command.Parameters.AddWithValue("@3", fn);
command.Parameters.AddWithValue("@4", Request.QueryString["kurseID"]);
await command.ExecuteNonQueryAsync();
File1.Visible = false;
Submit1.Visible = false;
Button2.Visible = true;
Label1.Text = "Задание сдано";
for_div.Attributes.CssStyle.Add("background-color", "#cef3c0");
}
private async void Button_Click_1(object sender, RoutedEventArgs e)
{
if (textBox.Text == "")
{
textBox.Background = Brushes.Red;
textBox.Opacity = 0.4;
return;
}
SqlConnection = new OleDbConnection(connectString);
await SqlConnection.OpenAsync();
OleDbCommand command1 = new OleDbCommand("INSERT INTO [Персонал] (ФИО,[Дата устройства],Салон)VALUES(@1,@2,@3)", SqlConnection);
command1.Parameters.AddWithValue("1", textBox.Text);
command1.Parameters.AddWithValue("2", DateTime.Now.Day + "." + DateTime.Now.Month + "." + DateTime.Now.Year);
if (this.Tag.ToString() == "false")
{
command1.Parameters.AddWithValue("3", false);
}
else
{
command1.Parameters.AddWithValue("3", true);
}
await command1.ExecuteNonQueryAsync();
DataGrid1.Items.Add(new Item()
{
a_1 = textBox.Text, a_2 = DateTime.Now.Day + "." + DateTime.Now.Month + "." + DateTime.Now.Year, a_3 = "0"
});
textBox.Text = "";
}
/// <summary>
/// Saves the object if it already exists in the db, or creates it in the db.
/// </summary>
public async void SaveAsync()
{
OleDbConnection conn;
OleDbCommand command;
conn = new OleDbConnection(AppConfiguration.connectionString);
conn.Open();
if (ID.Equals(new Guid()))
{
//new record, insert it into db and update the ID property of the current object
command = new OleDbCommand("INSERT INTO Class(ID, SchoolYear, Course) VALUES (@ID, @SchoolYear, @Course)",
conn);
command.Parameters.AddWithValue("ID", Guid.NewGuid());
command.Parameters.AddWithValue("SchoolYear", SchoolYear);
command.Parameters.AddWithValue("Course", Course);
ID = (Guid)(command.Parameters["ID"].Value);
await command.ExecuteNonQueryAsync();
}
else
{
//existing record, update it
command = new OleDbCommand("UPDATE Classes SET SchoolYear=@SchoolYear, Course=@Course WHERE ID=@ID",
conn);
command.Parameters.AddWithValue("SchoolYear", SchoolYear);
command.Parameters.AddWithValue("Course", Course);
command.Parameters.AddWithValue("ID", ID);
await command.ExecuteNonQueryAsync();
}
conn.Close();
}
public async Task <bool> UseCode(string code)
{
if (await CodeExists(code))
{
try
{
connection.Open();
OleDbCommand command = new OleDbCommand();
command.Connection = connection;
command.CommandText = $"DELETE * from Discounts where Code = ?";
await command.ExecuteNonQueryAsync();
return(true);
}
catch (OleDbException dbException)
{
throw new Exception(dbException.Message);
}
finally
{
connection.Close();
}
}
return(false);
}
public async Task CreateTableIfNotExists()
{
using (OleDbConnection connection = new OleDbConnection(Settings.ConnectionString))
{
await connection.OpenAsync();
using (OleDbCommand command = connection.CreateCommand())
{
StringBuilder query = new StringBuilder();
query.Append($" if object_id({Settings.DatabaseTableToStoreLog.ToSqlString()}) is null ");
query.Append($" create table [{Settings.DatabaseTableToStoreLog}] ");
query.Append($" ( ");
query.Append($" [{"id".ToConventionPattern()}] bigint identity(1,1) ");
query.Append($" ,[{"system".ToConventionPattern()}] varchar(50) ");
query.Append($" ,[{"module".ToConventionPattern()}] varchar(50) ");
query.Append($" ,[{"version".ToConventionPattern()}] varchar(50) ");
query.Append($" ,[{"user".ToConventionPattern()}] varchar(50) ");
query.Append($" ,[{"date".ToConventionPattern()}] datetime ");
query.Append($" ,[{"info".ToConventionPattern()}] varchar(max) ");
query.Append($" ,[{"info_description".ToConventionPattern()}] varchar(100) ");
query.Append($" ,[{"level".ToConventionPattern()}] varchar(50) ");
query.Append($" ) ");
command.CommandText = query.ToString();
await command.ExecuteNonQueryAsync();
}
}
}
public async Task <bool> AutorizeAsync(string token, Chat chat)
{
if (string.IsNullOrEmpty(token) || chat == null)
{
return(false);
}
try
{
var command = new OleDbCommand($"SELECT managerid, telegramchatid, token FROM telegram_managers where token='{token.Replace("\'", "\'\'")}';".Replace("\'\'", "NULL").Replace("NULLNULL", "\'\'\'\'"), Connection);
var reader = await command.ExecuteReaderAsync();
if (!reader.Read())
{
return(false);
}
var updcommand = new OleDbCommand($"UPDATE telegram_managers SET telegramchatid ={chat.Id}, token=null where token='{token?.Replace("\'", "\'\'")}'; ".Replace("\'\'", "NULL").Replace("NULLNULL", "\'\'\'\'"), Connection);
await updcommand.ExecuteNonQueryAsync();
return(true);
}
catch (Exception e)
{
Log.Add(new Log.LogMessage(Log.MessageType.ERROR, "AutorizeAsync: " + e.Message));
#if DEBUG
throw;
#endif
return(false);
}
}
// TODO: отсылка прикрепленных объектов и нормальная замена их в бд
public async Task <List <Message> > GetUnsendedMessages()
{
try
{
var messages = new DataTable();
messages.Load(await new OleDbCommand($"SELECT message_id, from_id, date, chat_id, text FROM telegram_messages where issended=0;", Connection).ExecuteReaderAsync());
var command = new OleDbCommand("DELETE FROM telegram_messages WHERE message_id<0;", Connection);
await command.ExecuteNonQueryAsync();
return(Enumerable.Select(messages.AsEnumerable(), row => new Message
{
MessageId = int.Parse(row[0].ToString()),
Chat = new Chat {
Id = int.Parse(row[3].ToString())
},
Text = row[4].ToString()
}).ToList());
}
catch (Exception e)
{
Log.Add(new Log.LogMessage(Log.MessageType.ERROR, "GetUnsendedMessages: " + e.Message));
#if DEBUG
throw;
#endif
return(new List <Message>(0));
}
}
public async void InsertOrUpdateClient(User user, Contact contact = null)
{
if (user == null)
{
return;
}
try
{
InsertOrUpdateUser(user);
InsertContact(contact);
var test = new OleDbCommand($"select * from telegram_clients where telegram_user_id={user.Id}", Connection);
var command = new OleDbCommand()
{
Connection = Connection,
CommandText =
((test.ExecuteReader()?.Read() ?? false) && contact != null)
? $"UPDATE telegram_clients SET phone = '{contact.PhoneNumber?.Replace("\'", "\'\'")}' WHERE telegram_user_id = {user.Id};".Replace("\'\'", "NULL").Replace("NULLNULL", "\'\'\'\'")
: $"INSERT INTO telegram_clients (phone,telegram_user_id) VALUES('{contact?.PhoneNumber?.Replace("\'", "\'\'")}', {user.Id});".Replace("\'\'", "NULL").Replace("NULLNULL", "\'\'\'\'")
};
await command.ExecuteNonQueryAsync();
}
catch (Exception e)
{
Log.Add(new Log.LogMessage(Log.MessageType.ERROR, "InsertOrUpdateClient: " + e.Message));
#if DEBUG
throw;
#endif
}
}
public async void InsertOrUpdateUser(User user)
{
if (user == null)
{
return;
}
try
{
var test = new OleDbCommand($"select * from telegram_users where id={user.Id};", Connection);
var command = new OleDbCommand()
{
Connection = Connection,
CommandText =
test.ExecuteReader()?.Read() ?? false
? $"UPDATE telegram_users SET first_name = '{user.FirstName?.Replace("\'", "\'\'")}', last_name = '{user.LastName?.Replace("\'", "\'\'")}', username = '******'", "\'\'")}' WHERE id = {user.Id};".Replace("\'\'", "NULL").Replace("NULLNULL", "\'\'\'\'")
: $"INSERT INTO telegram_users (id, first_name, last_name, username) VALUES ({user.Id}, '{user.FirstName?.Replace("\'", "\'\'")}', '{user.LastName?.Replace("\'", "\'\'")}', '{user.Username?.Replace("\'", "\'\'")}');".Replace("\'\'", "NULL").Replace("NULLNULL", "\'\'\'\'")
};
await command.ExecuteNonQueryAsync();
}
catch (Exception e)
{
Log.Add(new Log.LogMessage(Log.MessageType.ERROR, $"InsertOrUpdateUser: {e.Message}"));
#if DEBUG
throw;
#endif
}
}
/// <summary>
/// Saves the object if it already exists in the db, or creates it in the db.
/// </summary>
public async void SaveAsync()
{
OleDbConnection conn;
OleDbCommand command;
conn = new OleDbConnection(AppConfiguration.connectionString);
conn.Open();
if (ID.Equals(new Guid()))
{
//new record, insert it into db and update the ID property of the current object
command = new OleDbCommand("INSERT INTO Answers (ID, Description, Correct, QUESTION_ID) VALUES (@ID, @Description, @Correct, @QuestionID)",
conn);
command.Parameters.AddWithValue("ID", ID = Guid.NewGuid());
command.Parameters.AddWithValue("Description", Description);
command.Parameters.AddWithValue("Correct", Correct);
command.Parameters.AddWithValue("QuestionID", QuestionID);
await command.ExecuteNonQueryAsync();
}
else
{
//existing record, update it
//EXPLAIN: boolean to integer conversion
//EXPLAIN: unchangeable fields (QUESTION ID)
command = new OleDbCommand("UPDATE Answers SET Description=@Description, Correct=@Correct WHERE ID = @ID", conn);
command.Parameters.AddWithValue("Description", Description);
command.Parameters.AddWithValue("Correct", Correct);
command.Parameters.AddWithValue("ID", ID);
await command.ExecuteNonQueryAsync();
}
conn.Close();
}
/// <summary>
/// SUCKS
/// </summary>
public async void UpdateStudentAsync(Class newClass)
{
OleDbConnection conn;
OleDbCommand command;
DbDataReader reader;
conn = new OleDbConnection(AppConfiguration.connectionString);
conn.Open();
if (Class.ID != new Guid())
{
command = new OleDbCommand("SELECT ID FROM Classes WHERE SchoolYear=@SchoolYear AND Course=@Course",
conn);
command.Parameters.AddWithValue("SchoolYear", newClass.SchoolYear);
command.Parameters.AddWithValue("Course", newClass.Course);
reader = await command.ExecuteReaderAsync();
if (reader.Read())
{
Class.ID = reader.GetGuid(0);
}
reader.Close();
}
command = new OleDbCommand("UPDATE Students CLASS_ID=@ClassID WHERE ID=@ID",
conn);
command.Parameters.AddWithValue("ClassID", Class.ID);
command.Parameters.AddWithValue("ID", ID);
await command.ExecuteNonQueryAsync();
conn.Close();
}
protected async void DelClick_1(object sender, EventArgs e)
{
if (Request.QueryString["questID"] != null)
{
string connectString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Request.PhysicalPath.Substring(0, Request.PhysicalPath.LastIndexOf('\\')) + @"\tol_db.mdb";
OleDbConnection SqlConnection = new OleDbConnection(connectString);
await SqlConnection.OpenAsync();
OleDbCommand command = new OleDbCommand("DELETE * FROM [Задания курсов] WHERE Код = @id", SqlConnection);
command.Parameters.AddWithValue("@id", Request.QueryString["questID"]);
try
{
await command.ExecuteNonQueryAsync();
Response.Redirect("~/Teacher_kurse_edit.aspx?userID=" + Request.QueryString["userID"] + "&kurseID=" + Request.QueryString["kurseID"], false);
}
catch (Exception ex)
{
Response.Write("<script>alert('" + ex.Message.ToString() + "..." + ex.Source.ToString() + "');</script>");
}
SqlConnection.Close();
}
}
protected async void AddQuest(object sender, EventArgs e)
{
string connectString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Request.PhysicalPath.Substring(0, Request.PhysicalPath.LastIndexOf('\\')) + @"\tol_db.mdb";
OleDbConnection SqlConnection = new OleDbConnection(connectString);
await SqlConnection.OpenAsync();
string fn = System.IO.Path.GetFileName(File2.PostedFile.FileName);
string SaveLocation = Server.MapPath("Content") + "\\" + TextBox1.Text + "_" + TextBox6.Text + ".docx";
try
{
File2.PostedFile.SaveAs(SaveLocation);
}
catch (Exception ex)
{
Response.Write("Error: " + ex.Message);
}
TextBox8.Text = fn;
OleDbCommand command = new OleDbCommand("INSERT INTO [Задания курсов] (Тема,Задание,Файл,[Код курса]) VALUES (@theme,@quest,@file,@course)", SqlConnection);
command.Parameters.AddWithValue("@theme", TextBox6.Text);
command.Parameters.AddWithValue("@quest", TextBox7.Text);
command.Parameters.AddWithValue("@file", TextBox1.Text + "_" + TextBox6.Text + ".docx");
command.Parameters.AddWithValue("@course", Request.QueryString["kurseID"]);
await command.ExecuteNonQueryAsync();
SqlConnection.Close();
}
/// <summary>
/// Creates and opens a SQL connection.
/// </summary>
/// <param name="cancellationToken">The cancellation token.</param>
/// <returns></returns>
/// <remarks>
/// The caller of this method is responsible for closing the connection.
/// </remarks>
private async Task <OleDbConnection> CreateConnectionAsync(CancellationToken cancellationToken = default(CancellationToken))
{
var con = new OleDbConnection(ConnectionString);
await con.OpenAsync(cancellationToken).ConfigureAwait(false);
if (m_ServerDefaultSettings == null)
{
var temp = new SqlServerEffectiveSettings();
await temp.ReloadAsync(con, null);
#if !Thread_Missing
Thread.MemoryBarrier();
#endif
m_ServerDefaultSettings = temp;
}
var sql = BuildConnectionSettingsOverride();
if (sql.Length > 0)
{
using (var cmd = new OleDbCommand(sql.ToString(), con))
await cmd.ExecuteNonQueryAsync();
}
return(con);
}
/// <summary>
/// Saves the object if it already exists in the db, or creates it in the db.
/// </summary>
public async void SaveAsync()
{
OleDbConnection conn;
OleDbCommand command;
conn = new OleDbConnection(AppConfiguration.connectionString);
conn.Open();
if (ID.Equals(new Guid()))
{
command = new OleDbCommand("INSERT INTO Questions(ID, Description, TOPIC_ID) VALUES (@ID, @Description, @TopicID)",
conn);
command.Parameters.AddWithValue("ID", ID = Guid.NewGuid());
command.Parameters.AddWithValue("Description", Description);
command.Parameters.AddWithValue("TopicID", TopicID);
await command.ExecuteNonQueryAsync();
}
else
{
command = new OleDbCommand("UPDATE Questions SET Description=@Description WHERE ID = @ID",
conn);
command.Parameters.AddWithValue("Description", Description);
command.Parameters.AddWithValue("ID", ID);
await command.ExecuteNonQueryAsync();
}
conn.Close();
}
public async Task CreateTableIfNotExists()
{
using (OleDbConnection connection = new OleDbConnection(Settings.ConnectionString))
{
await connection.OpenAsync();
using (OleDbCommand command = connection.CreateCommand())
{
StringBuilder query = new StringBuilder();
query.Append($" create table if not exists `{Settings.DatabaseTableToStoreLog}` ");
query.Append($" ( ");
query.Append($" `{"id".ToConventionPattern()}` bigint not null auto_increment primary key ");
query.Append($" ,`{"system".ToConventionPattern()}` varchar(50) ");
query.Append($" ,`{"module".ToConventionPattern()}` varchar(50) ");
query.Append($" ,`{"version".ToConventionPattern()}` varchar(50) ");
query.Append($" ,`{"user".ToConventionPattern()}` varchar(50) ");
query.Append($" ,`{"date".ToConventionPattern()}` datetime ");
query.Append($" ,`{"info".ToConventionPattern()}` text ");
query.Append($" ,`{"info_description".ToConventionPattern()}` varchar(100) ");
query.Append($" ,`{"level".ToConventionPattern()}` varchar(50) ");
query.Append($" );");
command.CommandText = query.ToString();
await command.ExecuteNonQueryAsync();
}
}
}
/// <summary>
/// Saves the object if it already exists in the db, or creates it in the db.
/// </summary>
public async void SaveAsync()
{
OleDbConnection conn;
OleDbCommand command;
conn = new OleDbConnection(AppConfiguration.connectionString);
conn.Open();
if (ID.Equals(new Guid()))
{
command = new OleDbCommand("INSERT INTO Evaluations(ID, TestDate, Grade, TOPIC_ID, STUDENT_ID, QUESTIONNAIRESESSION_ID) VALUES (@ID, @TestDate, @Grade, @TopicID, @StudentID, @QuestionnaireSessionID)",
conn);
command.Parameters.AddWithValue("ID", ID = Guid.NewGuid());
command.Parameters.Add(new OleDbParameter("TestDate", OleDbType.Date)
{
Value = TestDate
});
command.Parameters.AddWithValue("Grade", Grade);
command.Parameters.AddWithValue("TopicID", TopicID);
command.Parameters.AddWithValue("StudentID", StudentID);
command.Parameters.AddWithValue("QuestionnaireSessionID", QuestionnaireSessionID);
await command.ExecuteNonQueryAsync();
}
else
{
command = new OleDbCommand("UPDATE Evaluations SET TestDate=@TestDate, Grade=@Grade WHERE ID = @ID",
conn);
command.Parameters.AddWithValue("TestDate", TestDate);
command.Parameters.AddWithValue("Grade", Grade);
command.Parameters.AddWithValue("ID", ID);
await command.ExecuteNonQueryAsync();
}
conn.Close();
}
private async Task <object> ExecuteNonQuery(string connectionString, List_val prep)
{
try
{
if (prep.Values != null)
{
prep = Create_non_parametr(prep);
}
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
await connection.OpenAsync();
using (OleDbCommand command = new OleDbCommand(prep.Query, connection))
{
if (prep.Values != null)
{
if (prep.Values.Count > 0)
{
foreach (Prep_val itm in prep.Values)
{
command.Parameters.Add(new OleDbParameter(itm.Val_name, GetOleDbType(itm.Type), itm.Len)).Value = Convert.ChangeType(itm.Value, itm.Value.GetType());
}
}
command.Prepare();
}
return(await command.ExecuteNonQueryAsync());
}
}
}
catch (Exception e)
{
throw new Exception("ExecuteNonQuery Error ", e);
}
}
public async Task CreateTableIfNotExists()
{
using (OleDbConnection connection = new OleDbConnection(Settings.ConnectionString))
{
await connection.OpenAsync();
using (OleDbCommand command = connection.CreateCommand())
{
StringBuilder query = new StringBuilder();
query.Append($" create table if not exists {Settings.DatabaseTableToStoreLog} ");
query.Append($" ( ");
query.Append($" \"{"id".ToConventionPattern()}\" serial ");
query.Append($" ,\"{"system".ToConventionPattern()}\" varchar(50) ");
query.Append($" ,\"{"module".ToConventionPattern()}\" varchar(50) ");
query.Append($" ,\"{"version".ToConventionPattern()}\" varchar(50) ");
query.Append($" ,\"{"user".ToConventionPattern()}\" varchar(50) ");
query.Append($" ,\"{"date".ToConventionPattern()}\" timestamp ");
query.Append($" ,\"{"info".ToConventionPattern()}\" varchar ");
query.Append($" ,\"{"info_description".ToConventionPattern()}\" varchar(100) ");
query.Append($" ,\"{"level".ToConventionPattern()}\" varchar(50) ");
query.Append($" );");
command.CommandText = query.ToString();
await command.ExecuteNonQueryAsync();
}
}
}
/// <summary>
/// 批量插入数据,事务处理
/// </summary>
/// <param name="sqlArray"></param>
private static void InsertTodataByBatch(String[] sqlArray)
{
try
{
if (aConnection == null)
{
aConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mdbpath);
}
if (aConnection.State == ConnectionState.Closed)
{
aConnection.OpenAsync();
}
OleDbTransaction transaction = aConnection.BeginTransaction();
aCommand.Connection = aConnection;
aCommand.Transaction = transaction;
for (int i = 0; i < sqlArray.Length; i++)
{
aCommand.CommandText = sqlArray[i];
//AsyncExecuteNonQuery(sqlArray[i], CallbackAsyncExecuteNonQuery, aConnection);
aCommand.ExecuteNonQueryAsync();
//LogHelper.log(Convert.ToString(i));
}
transaction.Commit();
// aConnection.Close();
}
catch (Exception e)
{
// LogHelper.log(e.Message);
}
}
/// <summary>
/// Method to Insert Record in the Excel
/// S1. If the EmpNo =0, then the Operation is Skipped.
/// S2. If the Student is already exist, then it is taken for Update
/// </summary>
/// <param name="Emp"></param>
public async Task <bool> ManageExcelRecordsAsync(Student stud)
{
bool IsSave = false;
if (stud.StudentID != 0)
{
await Conn.OpenAsync();
Cmd = new OleDbCommand();
Cmd.Connection = Conn;
Cmd.Parameters.AddWithValue("@StudentID", stud.StudentID);
Cmd.Parameters.AddWithValue("@Name", stud.Name);
Cmd.Parameters.AddWithValue("@Email", stud.Email);
Cmd.Parameters.AddWithValue("@Class", stud.Class);
Cmd.Parameters.AddWithValue("@Address", stud.Address);
if (!IsStudentRecordExistAsync(stud).Result)
{
Cmd.CommandText = "Insert into [Sheet1$] values (@StudentID,@Name,@Email,@Class,@Address)";
}
else
{
Cmd.CommandText = "Update [Sheet1$] set StudentID=@StudentID,Name=@Name,Email=@Email,Class=@Class,Address=@Address where StudentID=@StudentID";
}
int result = await Cmd.ExecuteNonQueryAsync();
if (result > 0)
{
IsSave = true;
}
Conn.Close();
}
return(IsSave);
}
private async void Button_Copy16_Click_1_44(object sender, RoutedEventArgs e)
{
SqlConnection = new OleDbConnection(connectString);
await SqlConnection.OpenAsync();
OleDbDataReader sqlReader = null;
OleDbCommand command1 = new OleDbCommand("INSERT INTO [Клиенты] ([Фамилия], [Имя], Отчество, Адрес, [E-mail], Телефон)VALUES(@LN, @FN, @Fam, @Adress, @Usl, @Price)", SqlConnection);
command1.Parameters.AddWithValue("LN", textBox_Copy.Text);
command1.Parameters.AddWithValue("FN", textBox_Copy1.Text);
command1.Parameters.AddWithValue("Fam", textBox_Copy2.Text);
command1.Parameters.AddWithValue("Adress", textBox_Copy3.Text);
command1.Parameters.AddWithValue("Usl", textBox_Copy4.Text);
command1.Parameters.AddWithValue("Price", textBox.Text);
if (textBox_Copy.Text != "")
{
if (textBox_Copy1.Text != "")
{
if (textBox_Copy2.Text != "")
{
if (textBox_Copy3.Text != "")
{
if (textBox_Copy4.Text != "")
{
if (textBox.Text != "")
{
await command1.ExecuteNonQueryAsync();
MessageBox.Show("Данные успешно добавлены");
textBox_Copy.Text = "";
textBox_Copy1.Text = "";
textBox_Copy2.Text = "";
textBox_Copy3.Text = "";
textBox_Copy4.Text = "";
textBox.Text = "";
return;
}
}
}
}
}
}
textBox.Background = Brushes.Red;
textBox.Opacity = 0.4;
textBox_Copy.Background = Brushes.Red;
textBox_Copy.Opacity = 0.4;
textBox_Copy1.Background = Brushes.Red;
textBox_Copy1.Opacity = 0.4;
textBox_Copy2.Background = Brushes.Red;
textBox_Copy2.Opacity = 0.4;
textBox_Copy3.Background = Brushes.Red;
textBox_Copy3.Opacity = 0.4;
textBox_Copy4.Background = Brushes.Red;
textBox_Copy4.Opacity = 0.4;
}