public RecievePay(List <PaymentOrder> list)
{
using (VistaDBConnection connection = new VistaDBConnection(new Connection().ConnectionString))
{
try
{
foreach (var pay in list)
{
connection.Open();
using (VistaDBCommand command = new VistaDBCommand())
{
command.Connection = connection;
command.CommandText = $"UPDATE dbo.PaymentOrder SET Status = 'Recieved',RecievedBy ={LoginUser.UserId},RecievedDate = GETDATE() WHERE Id = {pay.Id}";
command.ExecuteNonQuery();
connection.Close();
}
}
MessageBox.Show(@"Selected payments confirmed successfully");
}
catch (VistaDBException exception)
{
Log.Error(exception);
}
}
}
public static API_VistaDB executeNonQuery(this API_VistaDB vistaDB, string command)
{
"[API_VistaDB] Executing Non Query: {0}".info(command);
VistaDBConnection sqlConnection = null;
try
{
sqlConnection = new VistaDBConnection(vistaDB.ConnectionString);
sqlConnection.Open();
var sqlCommand = new VistaDBCommand();
sqlCommand.Connection = sqlConnection;
sqlCommand.CommandText = command;
sqlCommand.CommandType = CommandType.Text;
sqlCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
vistaDB.LastError = ex.Message;
"[executeNonQuery] {0}".error(ex.Message);
//ex.log();
}
finally
{
if (sqlConnection.notNull())
{
sqlConnection.Close();
}
}
return(vistaDB);
}
/// <summary>
/// Call the export schema and data sql function to write out the xml file
/// </summary>
/// <param name="outputFilename">Name of the file to write to disk</param>
public static void CallExportSchemaAndDataSQL(string outputFilename)
{
Console.WriteLine("Attempting to execute CLR Proc ExportSchemaAndData");
using (VistaDBConnection connection = new VistaDBConnection())
{
connection.ConnectionString = SampleRunner.ConnectionString;
connection.Open();
try
{
using (VistaDBCommand command = new VistaDBCommand())
{
// Straight forward way to call a function is just using SELECT
// You cannot EXEC a SqlFunction, and you cannot set the command here to be a stored proc
// Setting this command to a stored proc is a common error, the two are not the same
// SqlFunction = SELECT to call
// SqlProcdure = EXEC or direct call using StoredProcedure command type
command.Connection = connection;
command.CommandText = string.Format("SELECT ExportSchemaAndData('{0}');", outputFilename);
// This command does not return anything in the rowset, so just execute non query
command.ExecuteNonQuery();
}
Console.WriteLine(string.Format("Schema and Data export to {0}\\{1}.xml", Directory.GetCurrentDirectory(), outputFilename));
}
catch (Exception e)
{
Console.WriteLine("Failed to execute CLR-Proc ExportSchemaAndData, Reason: " + e.Message);
}
}
}
public static void Test()
{
FileInfo fi = new FileInfo(AppDomain.CurrentDomain.BaseDirectory + "\\Cases\\InitialProject.cs");
VistaDBConnection conn = new VistaDBConnection("Data Source=" + AppDomain.CurrentDomain.BaseDirectory + "\\App_Data\\FmqStore.vdb3");
conn.Open();
string sql = "insert into [FileSystem](FileName,FileDir,FileSize,HashCode,BinData,FileVersion,CreateDate,LastChangeDate) values("
+ "@FileName,@FileDir,@FileSize,@HashCode,@BinData,1, @CreateDate, @LastChangeDate"
+ ")";
VistaDBCommand cmd = new VistaDBCommand(sql, conn);
cmd.Parameters.AddWithValue("@FileName", fi.Name);
cmd.Parameters.AddWithValue("@FileDir", "/cases");
cmd.Parameters.AddWithValue("@FileSize", fi.Length);
byte[] fBin = GetFileBytes(fi.FullName);
cmd.Parameters.AddWithValue("@HashCode", GetMD5Hash(fBin));
cmd.Parameters.AddWithValue("@BinData", fBin);
cmd.Parameters.AddWithValue("@CreateDate", fi.CreationTimeUtc);
cmd.Parameters.AddWithValue("@LastChangeDate", fi.LastWriteTimeUtc);
cmd.ExecuteNonQuery();
conn.Close();
conn.Dispose();
}
esDataResponse IDataProvider.ExecuteNonQuery(esDataRequest request)
{
esDataResponse response = new esDataResponse();
VistaDBCommand cmd = null;
try
{
cmd = new VistaDBCommand();
if (request.CommandTimeout != null)
{
cmd.CommandTimeout = request.CommandTimeout.Value;
}
if (request.Parameters != null)
{
Shared.AddParameters(cmd, request);
}
switch (request.QueryType)
{
case esQueryType.TableDirect:
cmd.CommandType = CommandType.TableDirect;
cmd.CommandText = request.QueryText;
break;
case esQueryType.StoredProcedure:
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = Shared.CreateFullName(request);
break;
case esQueryType.Text:
cmd.CommandType = CommandType.Text;
cmd.CommandText = request.QueryText;
break;
}
try
{
esTransactionScope.Enlist(cmd, request.ConnectionString, CreateIDbConnectionDelegate);
response.RowsEffected = cmd.ExecuteNonQuery();
}
finally
{
esTransactionScope.DeEnlist(cmd);
}
if (request.Parameters != null)
{
Shared.GatherReturnParameters(cmd, request, response);
}
}
catch (Exception ex)
{
CleanupCommand(cmd);
response.Exception = ex;
}
return(response);
}
public int Execute(String sql)
{
int rows = 0;
VistaDBCommand cmd = new VistaDBCommand(sql, co);
try
{
rows = cmd.ExecuteNonQuery();
_lastError = "";
}
catch (Exception e)
{
_lastError = e.Message;
}
return(rows);
}
public static API_VistaDB executeNonQuery(this API_VistaDB vistaDB, VistaDBConnection sqlConnection, string command)
{
"[API_VistaDB] Executing Non Query: {0}".info(command);
try
{
var sqlCommand = new VistaDBCommand();
sqlCommand.Connection = sqlConnection;
sqlCommand.CommandText = command;
sqlCommand.CommandType = CommandType.Text;
sqlCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
vistaDB.LastError = ex.Message;
"[executeNonQuery] {0}".error(ex.Message);
//ex.log();
}
return(vistaDB);
}
public void PackageFile(FileInfo pkgFileInfo)
{
string sql = "insert into [FileSystem](FileName,FileDir,FileSize,HashCode,BinData,FileVersion,CreateDate,LastChangeDate) values("
+ "@FileName,@FileDir,@FileSize,@HashCode,@BinData,1, @CreateDate, @LastChangeDate"
+ ")";
VistaDBCommand cmd = new VistaDBCommand(sql, conn);
cmd.Parameters.AddWithValue("@FileName", pkgFileInfo.Name);
cmd.Parameters.AddWithValue("@FileDir", pkgFileInfo.DirectoryName.Replace(BaseDir, "").Replace("\\", "/"));
cmd.Parameters.AddWithValue("@FileSize", pkgFileInfo.Length);
byte[] fBin = InitialProject.GetFileBytes(pkgFileInfo.FullName);
cmd.Parameters.AddWithValue("@HashCode", InitialProject.GetMD5Hash(fBin));
cmd.Parameters.AddWithValue("@BinData", fBin);
cmd.Parameters.AddWithValue("@CreateDate", pkgFileInfo.CreationTimeUtc);
cmd.Parameters.AddWithValue("@LastChangeDate", pkgFileInfo.LastWriteTimeUtc);
cmd.ExecuteNonQuery();
cmd.Dispose();
}
/// <summary>
/// 同步文件
/// </summary>
/// <param name="currentFile">The current file.</param>
public void SynFileInfo(FileInfo currentFile)
{
//检查项目中是否存在该文件(目录+文件名)
//比较文件：是否更新？
string sql = string.Format("select top 1 FileID from [FileSystem] where ProjectID={0} AND FileDir='{1}' AND FileName='{2}'",
1,
currentFile.DirectoryName.Replace(BaseDir, "").Replace("\\", "/"),
currentFile.Name);
VistaDBCommand cmd = new VistaDBCommand(sql, conn);
object oFileID = cmd.ExecuteScalar();
if (oFileID == null)
{
PackageFile(currentFile);
}
else
{
#region 比较版本
#endregion
#region 插入新文件
sql = "insert into [FileSystem](FileName,FileDir,FileSize,HashCode,BinData,FileVersion,CreateDate,LastChangeDate) values("
+ "@FileName,@FileDir,@FileSize,@HashCode,@BinData,2, @CreateDate, @LastChangeDate"
+ ")";
VistaDBCommand cmdAdd = new VistaDBCommand(sql, conn);
cmd.Parameters.AddWithValue("@FileName", currentFile.Name);
cmd.Parameters.AddWithValue("@FileDir", currentFile.DirectoryName.Replace(BaseDir, "").Replace("\\", "/"));
cmd.Parameters.AddWithValue("@FileSize", currentFile.Length);
byte[] fBin = InitialProject.GetFileBytes(currentFile.FullName);
cmd.Parameters.AddWithValue("@HashCode", InitialProject.GetMD5Hash(fBin));
cmd.Parameters.AddWithValue("@BinData", fBin);
cmd.Parameters.AddWithValue("@CreateDate", currentFile.CreationTimeUtc);
cmd.Parameters.AddWithValue("@LastChangeDate", currentFile.LastWriteTimeUtc);
cmd.ExecuteNonQuery();
cmd.Dispose();
#endregion
}
}
public void Save(User user)
{
using (VistaDBConnection connection = new VistaDBConnection(new Connection().ConnectionString))
{
try
{
connection.Open();
using (VistaDBCommand command = new VistaDBCommand())
{
command.Connection = connection;
command.CommandText = $"INSERT INTO dbo.Users(RoleId,Name)VALUES({user.Type},N'{user.Name}')";
command.ExecuteNonQuery();
connection.Close();
MessageBox.Show($"successfully Added {user.Name}");
}
}
catch (VistaDBException exception)
{
MessageBox.Show(exception.Message);
}
}
}
public void CreateLogin(User user)
{
using (VistaDBConnection connection = new VistaDBConnection(new Connection().ConnectionString))
{
try
{
connection.Open();
using (VistaDBCommand command = new VistaDBCommand())
{
command.Connection = connection;
command.CommandText = $"INSERT INTO dbo.Login(UserName ,UserId ,Password)VALUES('{user.userName}',{user.Id},'{user.Password}')";
command.ExecuteNonQuery();
connection.Close();
MessageBox.Show($"successfully Added {user.userName}");
}
}
catch (VistaDBException exception)
{
MessageBox.Show(exception.Message);
}
}
}
public void User(User user)
{
using (VistaDBConnection connection = Connection.Connexion)
{
try
{
connection.Open();
using (VistaDBCommand command = new VistaDBCommand())
{
command.Connection = connection;
command.CommandText = $"INSERT INTO dbo.Users(Name,RoleId)VALUES('{user.Name}',2)";
command.ExecuteNonQuery();
connection.Close();
MessageBox.Show($"successfully added {user.Name}");
}
}
catch (VistaDBException exception)
{
MessageBox.Show("Something went wrong");
Log.Error(exception);
}
}
}
public void CategoryInterest(int id, double amount, string categoryName)
{
using (VistaDBConnection connection = Connection.Connexion)
{
try
{
connection.Open();
using (VistaDBCommand command = new VistaDBCommand())
{
command.Connection = connection;
command.CommandText = $"UPDATE dbo.SchoolType SET Amount ={amount} WHERE Id = {id}";
command.ExecuteNonQuery();
connection.Close();
MessageBox.Show($"Successfully updated Commission for {categoryName} Category");
}
}
catch (VistaDBException exception)
{
MessageBox.Show("Something went wrong");
Log.Error(exception);
}
}
}
public void Save(PaymentOrder paymentOrder)
{
using (VistaDBConnection connection = new VistaDBConnection(new Connection().ConnectionString))
{
try
{
connection.Open();
using (VistaDBCommand command = new VistaDBCommand())
{
command.Connection = connection;
command.CommandText = $"INSERT INTO dbo.PaymentOrder(PoNumber,Beneficiary,Payee,Phone,Amount,Interest,SchoolId,UserId)VALUES('{paymentOrder.PoNumber}','{paymentOrder.Beneficiary}','{paymentOrder.Payee}','{paymentOrder.Phone}',{paymentOrder.Amount},{paymentOrder.Interest},{SchoolId},{UserId})";
command.ExecuteNonQuery();
connection.Close();
MessageBox.Show(@"successfully Saved Payment");
}
}
catch (VistaDBException exception)
{
MessageBox.Show("Something went wrong");
Log.Error(exception);
}
}
}
public void Payment(PaymentOrder payment, School school, User user)
{
using (VistaDBConnection connection = Connection.Connexion)
{
try
{
connection.Open();
using (VistaDBCommand command = new VistaDBCommand())
{
command.Connection = connection;
command.CommandText = $"INSERT INTO dbo.PaymentOrder(PoNumber,Beneficiary,Payee,Phone,Amount,Interest,SchoolId,UserId)VALUES('{payment.PoNumber}','{payment.Beneficiary}','{payment.Payee}','{payment.Phone}',{payment.Amount},{payment.Interest},{school.Id},{user.Id},)";
command.ExecuteNonQuery();
connection.Close();
MessageBox.Show($"Successfully added Payment for {school.SchoolName}");
}
}
catch (VistaDBException exception)
{
MessageBox.Show("Something went wrong");
Log.Error(exception);
}
}
}
public void School(School school)
{
using (VistaDBConnection connection = Connection.Connexion)
{
try
{
connection.Open();
using (VistaDBCommand command = new VistaDBCommand())
{
command.Connection = connection;
//command.CommandText = $"INSERT INTO dbo.School(SchoolName)VALUES('{school.SchoolName}')";
command.CommandText = $"INSERT INTO dbo.School(TypeId,SchoolName)VALUES({school.TypeId},'{school.SchoolName}')";
command.ExecuteNonQuery();
connection.Close();
MessageBox.Show($"Successfully added {school.SchoolName}");
}
}
catch (VistaDBException exception)
{
MessageBox.Show("Something went wrong");
Log.Error(exception);
}
}
}
/// <summary>
/// Execute a VistaDBCommand (that returns no resultset) against the specified VistaDBConnection
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonQuery(conn, CommandType.Text, "Update TableTransaction set OrderAmount = 500 where ProdId=?", new VistaDBParameter("@prodid", 24));
/// </remarks>
/// <param name="connection">A valid VistaDBConnection</param>
/// <param name="commandType">The CommandType (TableDirect, Text)</param>
/// <param name="commandText">The T-SQL command</param>
/// <param name="commandParameters">An array of VistaDBParamters used to execute the command</param>
/// <returns>An int representing the number of rows affected by the command</returns>
public static int ExecuteNonQuery(VistaDBConnection connection, CommandType commandType, string commandText, params VistaDBParameter[] commandParameters)
{
if( connection == null ) throw new ArgumentNullException( "connection" );
// Create a command and prepare it for execution
VistaDBCommand cmd = new VistaDBCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, connection, (VistaDBTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection );
// Finally, execute the command
int retval = cmd.ExecuteNonQuery();
// Detach the VistaDBParameters from the command object, so they can be used again
cmd.Parameters.Clear();
if( mustCloseConnection )
connection.Close();
return retval;
}
/// <summary>
/// Call the Stored Proc version to get the database version
/// </summary>
public static void CallGetDatabaseVersionProcedureSQL()
{
Console.WriteLine("Attempting to execute CLR Procedure GetVersionProcedure");
using (VistaDBConnection connection = new VistaDBConnection(SampleRunner.ConnectionString))
{
connection.Open();
try
{
// Setup a command against the database like any other command, but then you have to change the command type
// to tell it you are calling a stored proc directly
using (VistaDBCommand command = new VistaDBCommand())
{
// Use our connection from above
command.Connection = connection;
// Put the name of the stored proc, you don't need to EXEC. This command will be called directly
// Be sure to include all the parameters
command.CommandText = "GetVersionProcedure(@versionout);";
command.CommandType = System.Data.CommandType.StoredProcedure; // Normally this is just text that is being executed
// Build up the parameter to the clr proc
VistaDBParameter outparam = new VistaDBParameter();
// This name has to match the entry in the commandtext
outparam.ParameterName = "@versionout";
// Telling it that this is an OUTPUT parameter
// This is how you should always get values back from a stored proc. The return value in a stored proc is really only
// meant to tell you the number of rows affected, not values.
outparam.Direction = System.Data.ParameterDirection.Output;
// Add it to the command
command.Parameters.Add(outparam);
// We are not expecting any return values, and the output parameters will still be filled out
// using ExecuteNonQuery. This saves object setup and teardown of a reader when we don't need it.
command.ExecuteNonQuery();
// Make sure the outparam is not null
if (outparam.Value != null)
{
// Print it to the console
Console.WriteLine(Convert.ToString(outparam.Value));
}
}
}
catch (Exception e)
{
Console.WriteLine("Failed to execute CLR Function GetVersionProcedure, Reason: " + e.Message);
}
}
}
private void InitializeDatabase()
{
string connectionString = ConnectionString;
Debug.AssertStringNotEmpty(connectionString);
if (File.Exists(_databasePath))
{
return;
}
//
// Make sure that we don't have multiple threads all trying to create the database
//
lock (_lock)
{
//
// Just double check that no other thread has created the database while
// we were waiting for the lock
//
if (File.Exists(_databasePath))
{
return;
}
VistaDBConnectionStringBuilder builder = new VistaDBConnectionStringBuilder(connectionString);
using (VistaDBConnection connection = new VistaDBConnection())
using (VistaDBCommand command = connection.CreateCommand())
{
string passwordClause = string.Empty;
if (!string.IsNullOrEmpty(builder.Password))
{
passwordClause = " PASSWORD '" + EscapeApostrophes(builder.Password) + "',";
}
// create the database using the webserver's default locale
command.CommandText = "CREATE DATABASE '" + EscapeApostrophes(_databasePath) + "'" + passwordClause + ", PAGE SIZE 1, CASE SENSITIVE FALSE;";
command.ExecuteNonQuery();
const string ddlScript = @"
CREATE TABLE [ELMAH_Error]
(
[ErrorId] INT NOT NULL,
[Application] NVARCHAR (60) NOT NULL,
[Host] NVARCHAR (50) NOT NULL,
[Type] NVARCHAR (100) NOT NULL,
[Source] NVARCHAR (60) NOT NULL,
[Message] NVARCHAR (500) NOT NULL,
[User] NVARCHAR (50) NOT NULL,
[StatusCode] INT NOT NULL,
[TimeUtc] DATETIME NOT NULL,
[AllXml] NTEXT NOT NULL,
CONSTRAINT [PK_ELMAH_Error] PRIMARY KEY ([ErrorId])
)
GO
ALTER TABLE [ELMAH_Error]
ALTER COLUMN [ErrorId] INT NOT NULL IDENTITY (1, 1)
GO
CREATE INDEX [IX_ELMAH_Error_App_Time_Id] ON [ELMAH_Error] ([TimeUtc] DESC, [ErrorId] DESC)";
foreach (string batch in ScriptToBatches(ddlScript))
{
command.CommandText = batch;
command.ExecuteNonQuery();
}
}
}
}
esDataResponse IDataProvider.ExecuteNonQuery(esDataRequest request)
{
esDataResponse response = new esDataResponse();
VistaDBCommand cmd = null;
try
{
cmd = new VistaDBCommand();
if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
if (request.Parameters != null) Shared.AddParameters(cmd, request);
switch (request.QueryType)
{
case esQueryType.TableDirect:
cmd.CommandType = CommandType.TableDirect;
cmd.CommandText = request.QueryText;
break;
case esQueryType.StoredProcedure:
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = Shared.CreateFullName(request);
break;
case esQueryType.Text:
cmd.CommandType = CommandType.Text;
cmd.CommandText = request.QueryText;
break;
}
try
{
esTransactionScope.Enlist(cmd, request.ConnectionString, CreateIDbConnectionDelegate);
response.RowsEffected = cmd.ExecuteNonQuery();
}
finally
{
esTransactionScope.DeEnlist(cmd);
}
if (request.Parameters != null)
{
Shared.GatherReturnParameters(cmd, request, response);
}
}
catch (Exception ex)
{
CleanupCommand(cmd);
response.Exception = ex;
}
return response;
}
/// <summary>
/// Execute a VistaDBCommand (that returns no resultset) against the specified VistaDBTransaction
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonQuery(trans, CommandType.Text, "Select * from TableTransaction where ProdId=?", new VistaDBParameter("@prodid", 24));
/// </remarks>
/// <param name="transaction">A valid VistaDBTransaction</param>
/// <param name="commandType">The CommandType (TableDirect, Text)</param>
/// <param name="commandText">The T-SQL command</param>
/// <param name="commandParameters">An array of VistaDBParamters used to execute the command</param>
/// <returns>An int representing the number of rows affected by the command</returns>
public static int ExecuteNonQuery(VistaDBTransaction transaction, CommandType commandType, string commandText, params VistaDBParameter[] commandParameters)
{
if( transaction == null ) throw new ArgumentNullException( "transaction" );
if( transaction != null && transaction.Connection == null ) throw new ArgumentException( "The transaction was rollbacked or commited, please provide an open transaction.", "transaction" );
// Create a command and prepare it for execution
VistaDBCommand cmd = new VistaDBCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, (VistaDBConnection)transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection );
// Finally, execute the command
int retval = cmd.ExecuteNonQuery();
// Detach the VistaDBParameters from the command object, so they can be used again
cmd.Parameters.Clear();
return retval;
}
tgDataResponse IDataProvider.ExecuteNonQuery(tgDataRequest request)
{
tgDataResponse response = new tgDataResponse();
VistaDBCommand cmd = null;
try
{
cmd = new VistaDBCommand();
if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
if (request.Parameters != null) Shared.AddParameters(cmd, request);
switch (request.QueryType)
{
case tgQueryType.TableDirect:
cmd.CommandType = CommandType.TableDirect;
cmd.CommandText = request.QueryText;
break;
case tgQueryType.StoredProcedure:
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = Shared.CreateFullName(request);
break;
case tgQueryType.Text:
cmd.CommandType = CommandType.Text;
cmd.CommandText = request.QueryText;
break;
}
try
{
tgTransactionScope.Enlist(cmd, request.ConnectionString, CreateIDbConnectionDelegate);
#region Profiling
if (sTraceHandler != null)
{
using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "ExecuteNonQuery", System.Environment.StackTrace))
{
try
{
response.RowsEffected = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
esTrace.Exception = ex.Message;
throw;
}
}
}
else
#endregion Profiling
{
response.RowsEffected = cmd.ExecuteNonQuery();
}
}
finally
{
tgTransactionScope.DeEnlist(cmd);
}
if (request.Parameters != null)
{
Shared.GatherReturnParameters(cmd, request, response);
}
}
catch (Exception ex)
{
CleanupCommand(cmd);
response.Exception = ex;
}
return response;
}