public bool EliminarAlumno( string Codigo)
{
//Creo el nuevo objeto SQLconexion a la variable StrConexion.
try
{
Conexion = new SqlConnection("Data Source = localhost; Initial Catalog = BDAcademico; Integrated Security = True");
string sql = "DELETE FROM TablaAlumnos WHERE Codigo = '" + Codigo +"'";
SqlCommand cmd = new SqlCommand(sql, Conexion);
Conexion.Open();
cmd.BeginExecuteNonQuery();
return true;
}
catch (Exception ex)
{
return false;
}
Conexion.Close();
}
public bool insertContract(Contract contract)
{
using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(text))
{
using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand())
{
cmd.Connection = con;
con.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "spInsertContract";
cmd.Parameters.AddWithValue("@id", contract.ContractID);
cmd.Parameters.AddWithValue("@Title", contract.Title);
cmd.Parameters.AddWithValue("@ContractDate", contract.ContractDate);
cmd.Parameters.AddWithValue("@Value", contract.TotalValue);
cmd.Parameters.AddWithValue("@StatusID", contract.StatusID);
cmd.Parameters.AddWithValue("@ContractTypeId", contract.ContracTypeID);
cmd.Parameters.AddWithValue("@ActiveFlag", 'A');
cmd.Parameters.AddWithValue("@SignCount", contract.SignCount);
IAsyncResult result = cmd.BeginExecuteNonQuery();
cmd.EndExecuteNonQuery(result);
con.Close();
return(result.IsCompleted);
}
}
}
public int RunCommandCount(string commandText)
{
using (SqlConnection connection =
new SqlConnection(connectionString))
{
try
{
SqlCommand command = new SqlCommand(commandText, connection);
connection.Open();
IAsyncResult result = command.BeginExecuteNonQuery();
return command.EndExecuteNonQuery(result);
}
catch (InvalidOperationException ex)
{
Console.WriteLine("Error: {0}", ex.Message);
return 0;
}
catch (Exception ex)
{
Console.WriteLine("Error: {0}", ex.Message);
return 0;
}
}
}
protected void Confirmation(object sender, EventArgs e)
{
Customer cust = new Customer();
List<Customer> customersList = Application["CustomerList"] as List<Customer>;
cust.EmailAddress = email.Text.ToString();
cust.Password = pass1.Text.ToString();
cust.LastName = lname.Text.ToString();
cust.FirstName = fname.Text.ToString();
cust.FullAddress = address1.Text.ToString();
cust.ContactPhone = phone.Text.ToString();
SqlConnection connect = new SqlConnection("Data Source=dcm.uhcl.edu;Initial Catalog=c423013fa01kannegantis;User id=kannegantis;Password=1187207;Persist Security Info=True");
String Stat = "insert into KannegantiS_WADfl13_Customers(emailAddress,password,firstName,lastName,fullAddress,contactPhone) values('" + cust.EmailAddress + "','" + cust.Password + "','" + cust.FirstName + "','" + cust.LastName + "','" + cust.FullAddress + "','" + cust.ContactPhone + "')";
SqlCommand sc = new SqlCommand(Stat, connect);
try
{
connect.Open();
sc.CommandText = Stat;
sc.BeginExecuteNonQuery();
//System.Data.SqlClient.SqlDataReader sqlReader = sc.ExecuteReader();
}
finally
{
connect.Close();
}
Session["currentCutomer"] = cust;
customersList.Add(cust);
ClientScript.RegisterStartupScript(GetType(), "TestAlert", "alert(\'Thank you for creating an account.Please click the \\'Go to Main Page\\' button below and click \\'My Account\\' on the main page to Login.\');", true);
}
/// <summary>
/// 依SQL指令執行
/// </summary>
/// <param name="sSQL"></param>
/// <returns></returns>
public int ExecCmdAsync(string sSQL)
{
int RetV = 0;
try
{
CheckDBConnection();
if (_bHasTrans)
{
System.Data.SqlClient.SqlCommand Cmd = new System.Data.SqlClient.SqlCommand(sSQL, DbConn, _trans);
IAsyncResult cres = Cmd.BeginExecuteNonQuery(null, null);
RetV = Cmd.EndExecuteNonQuery(cres);
}
else
{
DbConn.Open();
System.Data.SqlClient.SqlCommand Cmd = new System.Data.SqlClient.SqlCommand(sSQL, DbConn);
//Cmd.CommandTimeout = 1500;
//Cmd.EndExecuteNonQuery();
IAsyncResult cres = Cmd.BeginExecuteNonQuery(null, null);
RetV = Cmd.EndExecuteNonQuery(cres);
DbConn.Close();
}
}
catch (Exception ex)
{
string strMsg = "SQL指令執行失敗:" + sSQL + "\r\n"
+ ex.Message;
throw new Exception(strMsg);
}
return(RetV);
}
private void bttnClearDatabase_Click(object sender, EventArgs e)
{
string destroyTable = "DROP TABLE Records;";
SqlCommand myCommand;
myCommand = new SqlCommand();
myCommand.CommandText = destroyTable;
myCommand.Connection = myConnection;
myCommand.BeginExecuteNonQuery();
while (listBoxRecords.Items.Count != 0)
{
listBoxRecords.Items.RemoveAt(0);
}
}
/// <summary> Execute an asynchronous non-query SQL statement or stored procedure </summary>
/// <param name="DbType"> Type of database ( i.e., MSSQL, PostgreSQL ) </param>
/// <param name="DbConnectionString"> Database connection string </param>
/// <param name="DbCommandType"> Database command type </param>
/// <param name="DbCommandText"> Text of the database command, or name of the stored procedure to run </param>
/// <param name="DbParameters"> Parameters for the SQL statement </param>
public static void BeginExecuteNonQuery(EalDbTypeEnum DbType, string DbConnectionString, CommandType DbCommandType, string DbCommandText, List<EalDbParameter> DbParameters)
{
if (DbType == EalDbTypeEnum.MSSQL)
{
// Create the SQL connection
SqlConnection sqlConnect = new SqlConnection(DbConnectionString);
try
{
sqlConnect.Open();
}
catch (Exception ex)
{
throw new ApplicationException("Unable to open connection to the database." + Environment.NewLine + ex.Message, ex);
}
// Create the SQL command
SqlCommand sqlCommand = new SqlCommand(DbCommandText, sqlConnect)
{
CommandType = DbCommandType
};
// Copy all the parameters to this adapter
sql_add_params_to_command(sqlCommand, DbParameters);
// Run the command itself
try
{
sqlCommand.BeginExecuteNonQuery();
}
catch (Exception ex)
{
throw new ApplicationException("Error executing non-query command." + Environment.NewLine + ex.Message, ex);
}
// Return
return;
}
if (DbType == EalDbTypeEnum.PostgreSQL)
{
throw new ApplicationException("Support for PostgreSQL with SobekCM is targeted for early 2016");
}
throw new ApplicationException("Unknown database type not supported");
}
private static void RunCommandAsynchronously(
string commandText, string connectionString)
{
using (SqlConnection connection =
new SqlConnection(connectionString))
{
try
{
int count = 0;
SqlCommand command =
new SqlCommand(commandText, connection);
connection.Open();
IAsyncResult result =
command.BeginExecuteNonQuery();
while (!result.IsCompleted)
{
Console.WriteLine(
"Waiting ({0})", count++);
// Wait for 1/10 second, so the counter
// doesn't consume all available
// resources on the main thread.
System.Threading.Thread.Sleep(100);
}
Console.WriteLine(
"Command complete. Affected {0} rows.",
command.EndExecuteNonQuery(result));
}
catch (SqlException ex)
{
Console.WriteLine("Error ({0}): {1}",
ex.Number, ex.Message);
}
catch (InvalidOperationException ex)
{
Console.WriteLine("Error: {0}", ex.Message);
}
catch (Exception ex)
{
Console.WriteLine("Error: {0}", ex.Message);
}
}
}
private void bttnCreateDatabase_Click(object sender, EventArgs e)
{
string createTable = "CREATE TABLE Records " +
"(" +
"LastName VARCHAR(20) PRIMARY KEY," +
"FirstName VARCHAR(20), " +
"Address VARCHAR(30), " +
"City VARCHAR(20), " +
"State VARCHAR(20), " +
"Phone VARCHAR(20), " +
"Zip VARCHAR(20) " +
");";
SqlCommand myCommand;
myCommand = new SqlCommand();
myCommand.CommandText = createTable;
myCommand.Connection = myConnection;
myCommand.BeginExecuteNonQuery();
}
public bool updateContractStatus(int id)
{
using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(text))
{
using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand())
{
cmd.Connection = con;
con.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "spUpdateContractStatus";
cmd.Parameters.AddWithValue("@id", id);
IAsyncResult result = cmd.BeginExecuteNonQuery();
cmd.EndExecuteNonQuery(result);
con.Close();
return(result.IsCompleted);
}
}
}
public bool insertContractMember(ContractMember contractMember)
{
using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(text))
{
using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand())
{
cmd.Connection = con;
con.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "spInsertContractMember";
cmd.Parameters.AddWithValue("@CONTRACTID", contractMember.ContractID);
cmd.Parameters.AddWithValue("@MEMBERID", contractMember.MemberID);
cmd.Parameters.AddWithValue("@VALUE", contractMember.Value);
IAsyncResult result = cmd.BeginExecuteNonQuery();
cmd.EndExecuteNonQuery(result);
con.Close();
return(result.IsCompleted);
}
}
}
public bool insertContractDate(ContractDate contractDate)
{
using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(text))
{
using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand())
{
cmd.Connection = con;
con.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "spInsertContractDate";
cmd.Parameters.AddWithValue("@ContractId", contractDate.ContractID);
cmd.Parameters.AddWithValue("@No", contractDate.No);
if (contractDate.RedeemDate == null)
{
cmd.Parameters.AddWithValue("@RedeemDate", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@RedeemDate", contractDate.RedeemDate);
}
if (contractDate.SignDate == null)
{
cmd.Parameters.AddWithValue("@SignDate", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@SignDate", contractDate.SignDate);
}
IAsyncResult result = cmd.BeginExecuteNonQuery();
cmd.EndExecuteNonQuery(result);
con.Close();
return(result.IsCompleted);
}
}
}
public static void BeginExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
//create a command and prepare it for execution
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);
cmd.BeginExecuteNonQuery();
// detach the SqlParameters from the command object, so they can be used again.
cmd.Parameters.Clear();
}
private static void SampleAsyncMethods()
{
IAsyncResult asyncResult;
/***** SQL Connection *****/
// NOTE: "Async=true" setting required for asynchronous operations.
using (SqlConnection connection = new SqlConnection(@"Async=true;Server=SERVER;Database=DATABASE;Integrated Security=true"))
{
connection.Open();
using (SqlCommand cmd = new SqlCommand("SELECT UserId, Name, LastLogIn FROM Users WHERE Email = '*****@*****.**'", connection))
{
asyncResult = cmd.BeginExecuteReader();
// ... query executes asynchronously in background ...
using (IDataReader reader = cmd.EndExecuteReader(asyncResult))
{
// WARNING: The DbAsyncResult object returned by BeginExecuteReader always creates a ManualResetEvent, but
// never closes it; after calling EndExecuteReader, the AsyncWaitHandle property is still valid, so we close it explicitly.
asyncResult.AsyncWaitHandle.Close();
while (reader.Read())
{
// do stuff
}
}
}
using (SqlCommand cmd = new SqlCommand("UPDATE Users SET LastLogIn = GETUTCDATE() WHERE UserId = 1", connection))
{
asyncResult = cmd.BeginExecuteNonQuery();
// ... query executes asynchronously in background ...
int rowsAffected = cmd.EndExecuteNonQuery(asyncResult);
// WARNING: The DbAsyncResult object returned by BeginExecuteNonQuery always creates a ManualResetEvent, but
// never closes it; after calling EndExecuteReader, the AsyncWaitHandle property is still valid, so we close it explicitly.
asyncResult.AsyncWaitHandle.Close();
}
}
/***** File Operations *****/
// NOTE: FileOptions.Asynchronous flag required for asynchronous operations.
using (Stream stream = new FileStream(@"C:\Temp\test.dat", FileMode.Open, FileAccess.Read, FileShare.ReadWrite, 4096,
FileOptions.Asynchronous))
{
byte[] buffer = new byte[65536];
asyncResult = stream.BeginRead(buffer, 0, buffer.Length, null, null);
// ... disk read executes asynchronously in background ...
int bytesRead = stream.EndRead(asyncResult);
}
/***** HTTP Operation *****/
// WARNING: DNS operations are synchronous, and will block!
WebRequest request = WebRequest.Create(new Uri(@"http://www.example.com/sample/page"));
request.Method = "POST";
request.ContentType = "application/x-www-form-urlencoded";
asyncResult = request.BeginGetRequestStream(null, null);
// ... connection to server opened in background ...
using (Stream stream = request.EndGetRequestStream(asyncResult))
{
byte[] bytes = Encoding.UTF8.GetBytes("Sample request");
asyncResult = stream.BeginWrite(bytes, 0, bytes.Length, null, null);
stream.EndWrite(asyncResult);
}
// WARNING: WebRequest will swallow any exceptions thrown from the AsyncCallback passed to BeginGetResponse.
asyncResult = request.BeginGetResponse(null, null);
// ... web request executes in background ...
using (WebResponse response = request.EndGetResponse(asyncResult))
using (Stream stream = response.GetResponseStream())
{
// read response from server
// WARNING: This code should also use asynchronous operations (BeginRead, EndRead); "Using synchronous calls
// in asynchronous callback methods can result in severe performance penalties." (MSDN)
}
/***** DNS hostname resolution *****/
// WARNING: Doesn't truly use async I/O, but simply queues the request to a ThreadPool thread.
asyncResult = Dns.BeginGetHostEntry("www.example.com", null, null);
// ... DNS lookup executes in background
IPHostEntry entry = Dns.EndGetHostEntry(asyncResult);
/***** Other: Sockets, Serial Ports, SslStream *****/
}
private void save_Click(object sender, EventArgs e)
{
con.Open();
SqlCommand selcmd1 = new System.Data.SqlClient.SqlCommand("delete from studentdatabase.dbo.subject1 ", con);
selcmd1.BeginExecuteNonQuery();
con.Close();
con.Open();
DataTable dt1 = new DataTable();
SqlDataAdapter sda1 = new SqlDataAdapter("select * from studentdatabase.dbo.subject1", con);
sda1.Fill(dt1);
DataRow r1 = dt1.NewRow();
r1[0] = 1;
r1[1] = "" +s1.Text+"";
dt1.Rows.Add(r1);
SqlCommandBuilder cb1= new SqlCommandBuilder(sda1);
sda1.Update(dt1);
DataRow r2 = dt1.NewRow();
r2[0] = 2;
r2[1] = "" + s2.Text + "";
dt1.Rows.Add(r2);
SqlCommandBuilder cb2 = new SqlCommandBuilder(sda1);
sda1.Update(dt1);
DataRow r3 = dt1.NewRow();
r3[0] = 3;
r3[1] = "" + s3.Text + "";
dt1.Rows.Add(r3);
SqlCommandBuilder cb3 = new SqlCommandBuilder(sda1);
sda1.Update(dt1);
DataRow r4 = dt1.NewRow();
r4[0] = 4;
r4[1] = "" + s4.Text + "";
dt1.Rows.Add(r4);
SqlCommandBuilder cb4 = new SqlCommandBuilder(sda1);
sda1.Update(dt1);
DataRow r5 = dt1.NewRow();
r5[0] = 5;
r5[1] = "" + s5.Text + "";
dt1.Rows.Add(r5);
SqlCommandBuilder cb5 = new SqlCommandBuilder(sda1);
sda1.Update(dt1);
con.Close();
MessageBox.Show("Subject names are saved for 8th semester");
}
private void addSP(int key, bool retryFailures)
{
if (!hasFinished || isCancelled)
{
SqlConnection connection = new SqlConnection(this.connectionString + ";Asynchronous Processing=true");
SqlCommand cmd = new SqlCommand("dbo.TG_CreateValuations");
cmd.Connection = connection;
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param1 = new SqlParameter("ValuationRunID", this.valuationRunID);
param1.SqlDbType = SqlDbType.Int;
cmd.Parameters.Add(param1);
SqlParameter param2 = new SqlParameter("ValuationDate", this.valuationDate);
param2.SqlDbType = SqlDbType.DateTime;
cmd.Parameters.Add(param2);
SqlParameter param3 = new SqlParameter("Process", key);
param3.SqlDbType = SqlDbType.Int;
cmd.Parameters.Add(param3);
SqlParameter param4 = new SqlParameter("RetryFailures", retryFailures);
param4.SqlDbType = SqlDbType.Bit;
cmd.Parameters.Add(param4);
SqlParameter param5 = new SqlParameter("ErrorCount", SqlDbType.Int);
param5.Direction = ParameterDirection.Output;
cmd.Parameters.Add(param5);
cmd.CommandTimeout = 0;
commands[key] = cmd;
connection.Open();
if (runners != null)
{
AsyncCallback callback = new AsyncCallback(runValuations_Callback);
cmd.BeginExecuteNonQuery(callback, key);
runners[key] = true;
Debug.Print(string.Format("{0} - {1} started", DateTime.Now, key));
}
}
}
public IAsyncResult ExecuteNonQuery(string query)
{
SqlCommand sqlCommand = new SqlCommand(query);
SqlConnection connection = this.Connection;
sqlCommand.Connection = connection;
AsyncCallback callback = ((IAsyncResult result) =>
{
try
{
sqlCommand.EndExecuteNonQuery(result);
connection.Close();
}
catch (SqlException e)
{
throw e;
}
catch (Exception e)
{
throw e;
}
finally
{
if (connection != null)
{
if(connection.State == ConnectionState.Open)
connection.Close();
connection.Dispose();
}
if (sqlCommand != null)
{
sqlCommand.Dispose();
}
}
});
connection.Open();
return sqlCommand.BeginExecuteNonQuery(callback, sqlCommand);
}
/// <summary>
/// Executes a query statement asynchronously expecting no return value.
/// </summary>
/// <param name="query">SQL query.</param>
/// <param name="param">SQL parameter with name and value.</param>
/// <exception cref="System.ObjectDisposedException"></exception>
/// <exception cref="System.Threading.AbandonedMutexException"></exception>
/// <exception cref="System.InvalidOperationException"></exception>
/// <exception cref="System.Data.SqlClient.SqlException"></exception>
/// <exception cref="System.ArgumentException"></exception>
/// <exception cref="System.InvalidCastException"></exception>
/// <exception cref="System.ArgumentNullException"></exception>
public void BeginExecuteNonQuery(string query, params SqlParameter[] param)
{
try
{
_semaphore.WaitOne();
Open();
using (var command = new SqlCommand(query, _sqlConnection))
{
foreach (var p in param)
command.Parameters.Add(p);
command.Prepare();
command.BeginExecuteNonQuery(new AsyncCallback(HandleCallback), new Tuple<int, string, SqlCommand>(int.MinValue, string.Empty, command));
}
}
catch
{
Close();
_semaphore.Release();
throw;
}
}
/// <summary>
/// Updates the City field in the DB for a particular character.
/// </summary>
/// <param name="CharacterName">The name of the character to update.</param>
/// <param name="AccountName">The name of the account that has the character.</param>
/// <param name="CityName">The name of the city the character resides in.</param>
public static void UpdateCityForCharacter(string CharacterName, string CityName)
{
//Gets the data in the Name column.
SqlCommand Command = new SqlCommand("UPDATE Characters SET City='" + CityName +
"' WHERE Name='" + CharacterName + "'");
Command.BeginExecuteNonQuery(new AsyncCallback(EndUpdateCityForCharacter), new DatabaseAsyncObject(CityName,
CharacterName, Command));
}
static void Main(string[] args)
{
SqlConnectionStringBuilder cs = new SqlConnectionStringBuilder();
try
{
readConfigFile();
cs.DataSource = strDBServer;
cs.InitialCatalog = strDBInitialCatalog;
cs.ApplicationName = strApplicationName;
cs.IntegratedSecurity = true;
cs.AsynchronousProcessing = true;
string connectionString = cs.ToString();
stopWatch = new Stopwatch();
stopWatch.Start();
SqlConnection connCleanDB = new SqlConnection(connectionString);
connCleanDB.Open();
SqlCommand cmdCleanTable = new SqlCommand(sqlCleanTable, connCleanDB);
cmdCleanTable.CommandType = System.Data.CommandType.StoredProcedure;
cmdCleanTable.ExecuteNonQuery();
connCleanDB.Close();
localDateStarted = DateTime.Now;
for (int i = 1; i <= iNumberofIterations; i++)
{
SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
SqlCommand cmd = new SqlCommand(sqlAddData, conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add("@method", System.Data.SqlDbType.Int).Value = iMethod;
cmd.Parameters.Add("@id", System.Data.SqlDbType.Int).Value = i / 10;
if (iTransactionIsolation.Equals(TransactionIsolationLiteral.Unspecified))
{
cmd.BeginExecuteNonQuery(new AsyncCallback(EndExecution), cmd);
}
else
{
using ( new TransactionScope(TransactionScopeOption.RequiresNew, new TransactionOptions{IsolationLevel = iTransactionIsolation}))
{
cmd.BeginExecuteNonQuery(new AsyncCallback(EndExecution), cmd);
}
}
}
/*
Wait on all threads to complete
*/
while (iNumberofIterationsAttempted < iNumberofIterations)
{
Thread.Yield();
}
localDateCompleted = DateTime.Now;
stopWatch.Stop();
// Get the elapsed time as a TimeSpan value.
ts = stopWatch.Elapsed;
summarize(
connectionString
, ts
);
}
catch (Exception e)
{
Console.WriteLine("{0} Exception caught.", e);
}
}
/// <summary>
/// 数据库数据修改操作（插入、更新、删除等操作）
/// 直接调用，自动处理connection相关状态
/// </summary>
/// <param name="cmdText"></param>
/// <param name="connection"></param>
public static void ReviseDataToDataBase(string cmdText,SqlConnection connection)
{
SqlCommand cmd=new SqlCommand(cmdText,connection);
if(connection.State==ConnectionState.Closed)
connection.Open();//将与数据库的连接打开
try
{
IAsyncResult result= cmd.BeginExecuteNonQuery();//异步执行sql语句
//等待操作异步操作完成
while (true)
{
if (result.IsCompleted)
{
cmd.EndExecuteNonQuery(result); //结束异步BeginExecuteNonQuery
}
//待拓展
else
{
}
}
}
/////////异常处理/////////
catch (SqlException ex)
{
Debug.WriteLine("Error ({0}): {1}", ex.Number, ex.Message);
}
catch (InvalidOperationException ex)
{
Debug.WriteLine("Error: {0}", ex.Message);
}
catch (Exception ex)
{
// You might want to pass these errors
// back out to the caller.
Debug.WriteLine("Error: {0}", ex.Message);
}
}
/// <summary>
/// Creates a character in the DB.
/// </summary>
/// <param name="Character">The character that was created by a client.</param>
/// <param name="CServerListener">A CityServerListener instance, that can be used to
/// retrieve info about cityservers when sending a reply to the client.</param>
public static void CreateCharacter(LoginClient Client, Sim Character, ref CityServerListener CServerListener)
{
SqlCommand Command = new SqlCommand("INSERT INTO Characters(LastCached, Name, Sex) VALUES('" +
Character.Timestamp + "', '" + Character.Name + "', '" + Character.Sex + "')");
Command.BeginExecuteNonQuery(new AsyncCallback(EndCreateCharacter), new DatabaseAsyncObject(Client, Command,
ref CServerListener));
}
/// <summary>
/// Creates and executes a command object for inserting values to a table
/// </summary>
/// <param name="insert">The insert SQL string</param>
public void InsertToTable(string insert)
{
SqlCommand cmnd = new SqlCommand(insert, connection);
connection.Open();
cmnd.BeginExecuteNonQuery();
}
private void button1_Click(object sender, EventArgs e)
{
//this will add users and claims to the database.
int i = 0;
string[] names;
string[] VendorNames;
Random rnd1=new Random();
Random rndI=new Random();
names = new string[5] { "Customer1", "Customer2", "Customer3", "Customer4", "Customer5" };
VendorNames = new string[5] { "Vendor1", "Vendor2", "Vendor3", "Vendor4", "Vendor5" };
SqlConnection conn = new SqlConnection(Settings.Default.wtcwarrantyConnectionString);
conn.Open();
int claimID = 302;
try
{
for (i = 0; i < 100; i++)
{
int randomI = rndI.Next(0, 4);
int randName = rnd1.Next(0, 4);
claimID += i - (i - 1);
string vend = VendorNames[randomI];
string claiminsert = @"insert into claim_info(date,credit_on,claim_number,invoice,vendor,repair,credit_memo,comments,customer_w0)
values ('2012-02-02','2012-02-02','" + i + "','zzz" + i + "','" + vend + "','blah','bb2b" + i + "','Bleep Bloop','xxx" + i + "')";
string claimInsert = @"insert into claim(warranty_form, claim_id, customer_id,status)
values ('1122" +i+ "','" + claimID + "','23','Open')";
SqlCommand cmd = new SqlCommand(claiminsert, conn);
SqlCommand custInsert = new SqlCommand(claimInsert, conn);
cmd.BeginExecuteNonQuery();
custInsert.BeginExecuteNonQuery();
}
}
catch (SqlException error)
{
MessageBox.Show("Error: {} " + error);
}
}
/// <summary>
/// Creates a character in the DB.
/// </summary>
/// <param name="TimeStamp">When the character was last cached, should be equal to DateTime.Now</param>
/// <param name="Name">The name of the character.</param>
/// <param name="Sex">The sex of the character, should be MALE or FEMALE.</param>
public static void CreateCharacter(Sim Character)
{
SqlCommand Command = new SqlCommand("INSERT INTO Character(LastCached, Name, Sex) VALUES('" +
Character.Timestamp + "', '" + Character.Name + "', '" + Character.Sex + "')");
Command.BeginExecuteNonQuery(new AsyncCallback(EndCreateCharacter), Command);
}
public Sql ExecuteNonQuery(SqlCommand cmd, Action<Exception,int> callback = null)
{
cmd.Connection.Open();
var state = new ExecuteNonQueryState(cmd, callback);
cmd.BeginExecuteNonQuery(ExecuteNonQueryCallback, state);
return this;
}
private static void TestNonExecuteMethod()
{
Console.WriteLine("Staring of Non Execute Method....");
_reset.Reset();
try
{
using (SqlConnection conn = new SqlConnection(ConnectionStr))
{
using (SqlCommand cmd = new SqlCommand(StoredProc, conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
cmd.Connection.InfoMessage += ConnectionInfoMessage;
AsyncCallback result = NonQueryCallBack;
cmd.Connection.Open();
cmd.BeginExecuteNonQuery(result, cmd);
Console.WriteLine("Waiting for completion of executing stored procedure....");
_reset.WaitOne();
}
}
}
catch (SqlException ex)
{
Console.WriteLine("Problem with executing command! - [{0}]", ex.Message);
}
Console.WriteLine("Completion of Non Execute Method....");
}
private void submitnewclaimbtn_Click(object sender, EventArgs e)
{
//declare all variables for textboxes, date pickers,combo box, and rich text box
string customer = newCustomerComboBox.Text;
string warranty = newwarrantyfrmtb.Text;
string claimNum = newclaimnumtb.Text;
string invoice = newinvoicetb.Text;
string creditmemo = newcreditmemotb.Text;
string vendor = newvendortb.Text;
string repair = newrepairtb.Text;
DateTime date = newdatetp.Value;
DateTime crediton = newcreditontp.Value;
string comments = newcommentsrtb.Text;
string customerw0 = newcustomerw0tb.Text;
//declare sql statements
//get the customers name
string checkcustomerstatement = "select customer_name from customer where customer_name like'" + replaceQuote(customer) + "'";
//get customers id based on the customer name
string getcustid = "select customer_id from customer where customer_name like '" + replaceQuote(customer) + "'";
//get claim id based on the claim number
string getclaimid = "select claim_id from claim_info where claim_number like '" + claimNum + "'";
//insert the claims into the claim_info table
string claiminsert = @"insert into claim_info(date,credit_on,claim_number,invoice,vendor,repair,credit_memo,comments,customer_w0)
values ('" + date + "','" + crediton + "','" + claimNum + "','" + invoice + "','" + vendor + "','" + repair + "','" + creditmemo + "','" + comments + "','" +customerw0+"')";
//insert the customer name into the customer table
string insertCustomerName = @"insert into customer(customer_name) values('" + replaceQuote(customer) + "')";
//get the max claim id
string selectMaxClaimID = @"select max(claim_info.claim_id) from claim_info";
//open connection to SQL, declare SQL commands
//open sql connection
SqlConnection conn = new SqlConnection(Settings.Default.wtcwarrantyConnectionString);
//command for inserting a new claim
SqlCommand cmd = new SqlCommand(claiminsert, conn);
//command for checking the customers id
SqlCommand checkcustid = new SqlCommand(getcustid, conn);
//command for checking claim id
SqlCommand checkclaimid = new SqlCommand(getclaimid, conn);
//get customer name
SqlCommand custname = new SqlCommand(checkcustomerstatement, conn);
//open sql connection
try{
conn.Open();
//check to see if the name existis
SqlDataReader custread1 = custname.ExecuteReader();
//if the customer is already in the database then begin execution
if (custread1.Read() == true)
{
//close reader
custread1.Close();
//get customer id
int custidresult = ((int)checkcustid.ExecuteScalar());
//select cutomer name based on custoemr id
string sqlFindUsername = @"select customer_name from customer where customer_id ='" + custidresult.ToString() + "'";
SqlCommand getSQLCustName = new SqlCommand(sqlFindUsername, conn);
//get customer name
string sqlCustName = ((string)getSQLCustName.ExecuteScalar());
//insert claim information
cmd.ExecuteNonQuery();
//get claim info custoemr id
SqlCommand getClaimInfoCID = new SqlCommand(selectMaxClaimID, conn);
//get customer id
int claimID = ((int)getClaimInfoCID.ExecuteScalar());
//insert string into claim table
string claimInsert = @"insert into claim(warranty_form, claim_id, customer_id,status) values ('" + newwarrantyfrmtb.Text + "','" + claimID + "','" + custidresult.ToString() + "','Open')";
//insert the claim
SqlCommand insertClaimWarrant = new SqlCommand(claimInsert, conn);
//execute
insertClaimWarrant.ExecuteNonQuery();
//reload last claim
lastClaim();
//close connection
conn.Close();
if (claimsearchtb.Text != null)
{
//check if claim search is empty
loadDataGrid();
realTimeSearch();
openClaims();
}
else
{
loadDataGrid();
openClaims();
}
//clear all text fields
newCustomerComboBox.Text = null;
newwarrantyfrmtb.Text = null;
newclaimnumtb.Text = null;
newinvoicetb.Text = null;
newcreditmemotb.Text = null;
newvendortb.Text = null;
newrepairtb.Text = null;
newcommentsrtb.Text = null;
newcustomerw0tb.Text = null;
}
//if the customer is not in the databse ask if the user would like to add them
//if they answer yes it will add the user and then run the queries needed
//if they answer no it will simply close out and clear the claim.
else if (custread1.Read() == false)
{
//make sure the reader is closed
custread1.Close();
//ask the user if they would like to add the customer to the customer database
DialogResult diagResult = MessageBox.Show("This user is not part of the Customer Database. \n"
+ "Would you like to add it?", "No user found.", MessageBoxButtons.YesNo);
//if they answer yes then addd the user to the database, and submit the claim.
if (diagResult == DialogResult.Yes)
{
//Command to insert customer into the database
SqlCommand customerInsert = new SqlCommand(insertCustomerName, conn);
//execute the command and insert the customer
customerInsert.BeginExecuteNonQuery();
//return the customer name that was added to the database
MessageBox.Show(newCustomerComboBox.Text + " was added to the Customers Database.");
//submit the claim information to the database
submitClaims();
//success message
MessageBox.Show("Claim succesfully added to the database!");
}
else if (diagResult == DialogResult.No)
{
//tell the user the claim was not submitted
MessageBox.Show("Claim was not submitted. Please retry.");
//clear all text fields
newCustomerComboBox.Text = null;
newwarrantyfrmtb.Text = null;
newclaimnumtb.Text = null;
newinvoicetb.Text = null;
newcreditmemotb.Text = null;
newvendortb.Text = null;
newrepairtb.Text = null;
newcommentsrtb.Text = null;
newcustomerw0tb.Text = null;
}
//if the claim box isn't null, reload the grids and search along with openClaims
if (claimsearchtb.Text != null)
{
loadDataGrid();
realTimeSearch();
openClaims();
}
else
{
loadDataGrid();
openClaims();
}
}
this.claimsgridview.Sort(this.claimsgridview.Columns["status"], ListSortDirection.Ascending);
}
catch (SqlException exception)
{
MessageBox.Show("Error {0} : " +exception);
}
}
public static void ExcuteNonQueryPreparedStatement(SqlCommand command)
{
if (command == null)
{
return;
}
//Check for "'" in singular form causing a problem with the SQL Statment
foreach (object sqlParameter in from object sqlParameter in ((command.Parameters))
from item in
((IEnumerable) ((SqlParameter) (sqlParameter)).Value).Cast<object>()
.Where
(
item => item.ToString()
.Contains("'"))
select sqlParameter)
{
}
command.StatementCompleted += (SMECustContactCon_StatementCompleted);
try
{
IAsyncResult result = command.BeginExecuteNonQuery();
while (!result.IsCompleted)
{
Thread.Sleep(100);
}
if (command.Connection != null && command.Connection.State == ConnectionState.Closed)
{
return;
}
command.EndExecuteNonQuery(result);
}
catch (Exception ex)
{
MessageDialog.Show
(
"Error retrieved updating \r\n" + ex.InnerException,
"Error",
MessageDialog.MessageBoxButtons.Ok,
MessageDialog.MessageBoxIcon.Error,
"Please Check You Credentials Are Valid" + "\r\n" + ex.StackTrace);
}
}
/// <summary> Updates the cached links between aggregations and metadata, used by larger collections </summary>
/// <returns> TRUE if successful, otherwise FALSE </returns>
/// <remarks> This calls the 'Admin_Update_Cached_Aggregation_Metadata_Links' stored procedure.<br /><br />This runs asychronously as this routine may run for a minute or more.</remarks>
public static bool Admin_Update_Cached_Aggregation_Metadata_Links()
{
try
{
// Create the connection
using (SqlConnection connect = new SqlConnection(connectionString))
{
// Create the command
SqlCommand executeCommand = new SqlCommand("Admin_Update_Cached_Aggregation_Metadata_Links", connect)
{CommandType = CommandType.StoredProcedure};
// Create the data reader
connect.Open();
executeCommand.BeginExecuteNonQuery();
}
return true;
}
catch (Exception ee)
{
lastException = ee;
return false;
}
}
private void b_nonworking_Click(object sender, EventArgs e)
{
selectday.Visible = false;
dropdown.Visible = false;
lec1.Visible = false;
lec2.Visible = false;
lec3.Visible = false;
lec4.Visible = false;
lec5.Visible = false;
lec6.Visible = false;
lec7.Visible = false;
dd1.Visible = false;
dd2.Visible = false;
dd3.Visible = false;
dd4.Visible = false;
dd5.Visible = false;
dd6.Visible = false;
dd7.Visible = false;
saveme.Visible = false;
save.Visible = false;
s1.Visible = false;
s2.Visible = false;
s3.Visible = false;
s4.Visible = false;
s5.Visible = false;
daywise.Visible = false;
subwise.Visible = false;
rollname.Visible = false;
l_days.Visible = false;
m1.Visible = false;
t_name.Visible = false;
l_enterroll.Visible = false;
l_entername.Visible = false;
t_roll.Visible = false;
t_name.Visible = false;
b_submit.Visible = false;
l_putfinger.Visible = false;
pictureBox1.Visible = false;
l_dataenrolled.Visible = false;
m1.Visible = false;
m2.Visible = false;
m3.Visible = false;
m4.Visible = false;
m5.Visible = false;
m6.Visible = false;
m7.Visible = false;
m8.Visible = false;
m9.Visible = false;
m10.Visible = false;
m11.Visible = false;
m12.Visible = false;
m13.Visible = false;
m14.Visible = false;
m15.Visible = false;
m16.Visible = false;
m17.Visible = false;
m18.Visible = false;
m19.Visible = false;
m20.Visible = false;
m21.Visible = false;
m22.Visible = false;
m23.Visible = false;
m24.Visible = false;
m25.Visible = false;
m26.Visible = false;
m27.Visible = false;
m28.Visible = false;
m29.Visible = false;
m30.Visible = false;
m31.Visible = false;
studentlist.Visible = false;
con.Open();
SqlCommand selcmd1 = new System.Data.SqlClient.SqlCommand(" INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\\Test.xls;','select roll_no,name from studentdatabase.dbo.student3')", con);
selcmd1.BeginExecuteNonQuery();
con.Close();
}
static void Main(string[] args)
{
SqlConnection dbConnection = new SqlConnection();
dbConnection.ConnectionString = "Server=Baracus-Win8\\SQLEXPRESS;Database=cSharpPasswords;Trusted_Connection=True; MultipleActiveResultSets=True";
try
{
dbConnection.Open();
Console.WriteLine("DB is open");
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
Dictionary<int, string> passwords = new Dictionary<int, string>();
Dictionary<int, Tuple<string, string>> hashedPasswords = new Dictionary<int, Tuple<string, string>>();
string salt;
string hashedPassword;
SqlCommand command = new SqlCommand("SELECT userID,password FROM dbo.UserTable", dbConnection);
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
//get all userIDs, passwords from database table and add them to a dictionary.
passwords.Add(int.Parse(reader[0].ToString()), reader[1].ToString());
}
reader.Close();
foreach (KeyValuePair<int, string> kvp in passwords)
{
Console.WriteLine("Reading values from dictionary");
Console.WriteLine("UserID: {0}, Password: {1}", kvp.Key, kvp.Value);
Console.WriteLine("Hashing passwords...");
// hash passwords and save them in a new dictionary with the salt using the userID as the key
salt = BCryptHelper.GenerateSalt(12);
hashedPassword = hashPassword(kvp.Value.ToString(), salt);
Console.WriteLine("UserID: {0}, Hash: {1}", kvp.Key, hashedPassword);
hashedPasswords.Add(kvp.Key, Tuple.Create(hashedPassword, salt));
}
foreach (KeyValuePair<int, Tuple<string, string>> kvp in hashedPasswords)
{
//Insert the hashed passwords along with the salt back into the database
Console.WriteLine("Updating db password hashes");
string update;
update = System.String.Format("UPDATE dbo.UserTable SET " +
"dbo.UserTable.hashedPassword = N'{0}', " +
"dbo.UserTable.salt = N'{1}' " +
"WHERE dbo.UserTable.UserID = {2}", kvp.Value.Item1, kvp.Value.Item2, kvp.Key);
//Console.WriteLine("UserID: {0}, password hash: {1}", kvp.Key, kvp.Value);
Console.WriteLine(update);
SqlCommand insertCommand = new SqlCommand(update, dbConnection);
try
{
insertCommand.BeginExecuteNonQuery();
Thread.Sleep(1000);
}
catch (Exception e)
{
Console.WriteLine(e.Message); ;
}
}
Console.WriteLine("Database updated");
dbConnection.Close();
dbConnection.Dispose();
Console.ReadLine();
}
protected void pay_Click(object sender, EventArgs e)
{
String user1 = (String)Session["email"];
String lname = (String)Session["lname"];
String fname = (String)Session["fname"];
String date = DateTime.Today.ToString("MM/dd/yyyy");
String country = (String)Session["country"];
String cost = (String)Session["cost"];
String minutes = (String)Session["minutes"];
String cos = cost;
String cc = cos.Remove(0, 1);
float x = 0.06f;
float co = float.Parse(cc);
float final = co * x;
float finn = co + final;
tax.Text = Convert.ToString(final);
total.Text = Convert.ToString(finn);
String method = card.SelectedValue;
SqlConnection connect = new SqlConnection("Data Source=dcm.uhcl.edu;Initial Catalog=c423013fa01kannegantis;User id=kannegantis;Password=1187207;Persist Security Info=True");
string sc1 = string.Format("UPDATE KannegantiS_WADfl13_Customers SET country='" + country + "', orderDate= '" + date + "', numberofMinutes='" + minutes + "', dollaramount='" + cost + "',paymentMethod='" + method + "' WHERE emailAddress='" + user1 + "'");
SqlCommand sc = new SqlCommand(sc1, connect);
try
{
connect.Open();
sc.CommandText = sc1;
sc.BeginExecuteNonQuery();
}
catch (System.Data.SqlClient.SqlException ex)
{
string msg = "update Error:";
msg += ex.Message;
ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('" + msg + "');", true);
}
finally
{
connect.Close();
}
if (pay.Text != "Pay")
{
email();
Response.Redirect(@"http://www.paypal.com");
}
else if (pay.Text == "Pay")
{
email();
ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('An invoice has been sent to the email address in File. ');", true);
}
}
private IAsyncResult DoBeginExecuteNonQuery(SqlCommand command, bool disposeCommand, AsyncCallback callback, object state)
{
bool closeConnection = command.Transaction == null;
try
{
return WrappedAsyncOperation.BeginAsyncOperation(
callback,
cb => command.BeginExecuteNonQuery(cb, state),
ar => new DaabAsyncResult(ar, command, disposeCommand, closeConnection, DateTime.Now));
}
catch (Exception e)
{
instrumentationProvider.FireCommandFailedEvent(command.CommandText, ConnectionStringNoCredentials, e);
throw;
}
}
/// <summary>
/// Creates an account in the DB.
/// </summary>
/// <param name="AccountName">The accountname.</param>
/// <param name="Password">The password.</param>
public static void CreateAccount(string AccountName, string Password)
{
SqlCommand Command = new SqlCommand("INSERT INTO Accounts(AccountName, Password) VALUES('" +
AccountName + "', '" + Password + "')");
Command.BeginExecuteNonQuery(new AsyncCallback(EndCreateAccount), Command);
}