public double Recieved()
{
double Value = 0;
using (VistaDBConnection connection = new VistaDBConnection(new Connection().ConnectionString))
{
try
{
connection.Open();
using (VistaDBCommand command = new VistaDBCommand())
{
command.Connection = connection;
command.CommandText = $"SELECT dbo.Recieved('{SchoolName}')";
var reader = command.ExecuteReader();
while (reader.Read())
{
Value = reader.GetDouble(0);
}
connection.Close();
}
}
catch (VistaDBException exception)
{
MessageBox.Show(exception.Message);
}
}
recieved = Value;
return(recieved);
}
/// <summary>
/// Call the Sql Function version to get the database version
/// </summary>
public static void CallGetDatabaseVersionFunctionSQL()
{
Console.WriteLine("Attempting to execute CLR Function GetDatabaseVersionFunction");
using (VistaDBConnection connection = new VistaDBConnection(SampleRunner.ConnectionString))
{
connection.Open();
try
{
// 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
using (VistaDBCommand command = new VistaDBCommand())
{
command.Connection = connection;
command.CommandText = "SELECT GetVersionFunction();";
// The results are returned as a part of the standard rowset, so we only need to get back the first entry
Console.WriteLine(Convert.ToString(command.ExecuteScalar()));
}
}
catch (Exception e)
{
Console.WriteLine("Failed to execute CLR Function GetVersionFunction, Reason: " + e.Message);
}
}
}
public DataTable ViewTransactions(string schoolname)
{
var data = new DataTable();
using (VistaDBConnection connection = new VistaDBConnection(new Connection().ConnectionString))
{
try
{
connection.Open();
using (VistaDBCommand command = new VistaDBCommand())
{
command.Connection = connection;
var query = command.CommandText = $"SELECT po.Id AS 'Transaction Id',po.Beneficiary,po.Payee,po.Amount,po.Date,po.Status FROM PaymentOrder po WHERE po.SchoolId=(SELECT s.Id FROM School s WHERE s.SchoolName='{schoolname}') and po.RecievedDate BETWEEN GETDATE()-30 AND GETDATE()";
var adapter = new VistaDBDataAdapter(command.CommandText, command.Connection);
adapter.Fill(data);
connection.Close();
}
}
catch (VistaDBException exception)
{
MessageBox.Show("Something went wrong");
Log.Error(exception);
}
}
return(data);
}
/// <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 object executeScalar(this API_VistaDB vistaDB, string command)
{
"[API_VistaDB] Executing Scalar: {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;
return(sqlCommand.ExecuteScalar());
}
catch (Exception ex)
{
vistaDB.LastError = ex.Message;
"[executeNonQuery] {0}".error(ex.Message);
//ex.log();
}
finally
{
sqlConnection.Close();
}
return(null);
}
public double Amount()
{
using (VistaDBConnection connection = Connection.Connexion)
{
try
{
connection.Open();
using (VistaDBCommand command = new VistaDBCommand())
{
command.Connection = connection;
command.CommandText = $"SELECT Amount FROM dbo.SchoolType WHERE Id={TypeId}";
var reader = command.ExecuteReader();
while (reader.Read())
{
amount = reader.GetInt32(0);
}
connection.Close();
}
}
catch (VistaDBException exception)
{
MessageBox.Show(exception.Message);
}
}
return(amount);
}
public DataTable ViewPending()
{
var data = new DataTable();
using (VistaDBConnection connection = new VistaDBConnection(new Connection().ConnectionString))
{
try
{
connection.Open();
using (VistaDBCommand command = new VistaDBCommand())
{
command.Connection = connection;
var query = command.CommandText = $"SELECT Id,PoNumber,Beneficiary,Amount,(SELECT SchoolName FROM School s WHERE s.Id=p.SchoolId) AS 'School' FROM dbo.PaymentOrder p where status='Pending'";
var adapter = new VistaDBDataAdapter(command.CommandText, command.Connection);
adapter.Fill(data);
connection.Close();
}
}
catch (VistaDBException exception)
{
MessageBox.Show("Something went wrong");
Log.Error(exception);
}
}
return(data);
}
public static List <string> Names()
{
List <string> names = new List <string>();
using (VistaDBConnection connection = Connection.Connexion)
{
try
{
connection.Open();
using (VistaDBCommand command = new VistaDBCommand())
{
command.Connection = connection;
command.CommandText = $"SELECT Name FROM dbo.Users";
var reader = command.ExecuteReader();
while (reader.Read())
{
names.Add(reader.GetString(0));
}
connection.Close();
if (names.Count < 1)
{
names.Add("No Data Available");
}
}
}
catch (VistaDBException exception)
{
MessageBox.Show("Something went wrong");
Log.Error(exception);
}
}
return(names);
}
public static DataTable executeReader(this API_VistaDB vistaDB, string command)
{
var sqlConnection = new VistaDBConnection(vistaDB.ConnectionString);
sqlConnection.Open();
try
{
var sqlCommand = new VistaDBCommand();
sqlCommand.Connection = sqlConnection;
sqlCommand.CommandText = command;
sqlCommand.CommandType = CommandType.Text;
var reader = sqlCommand.ExecuteReader();
var dataTable = new DataTable();
dataTable.Load(reader);
return(dataTable);
}
catch (Exception ex)
{
vistaDB.LastError = ex.Message;
"[executeNonQuery] {0}".error(ex.Message);
//ex.log();
}
finally
{
if (sqlConnection.notNull())
{
sqlConnection.Close();
}
}
return(null);
}
public int FindId(User user)
{
int getId = 0;
using (VistaDBConnection connection = new VistaDBConnection(new Connection().ConnectionString))
{
try
{
connection.Open();
using (VistaDBCommand command = new VistaDBCommand())
{
command.Connection = connection;
command.CommandText = $"SELECT Id FROM dbo.Users where Name='{user.Name}'";
var reader = command.ExecuteReader();
while (reader.Read())
{
getId = reader.GetInt32(0);
}
connection.Close();
}
}
catch (VistaDBException exception)
{
MessageBox.Show(exception.Message);
}
}
return(getId);
}
private VistaDBConnection GetConnection(string connectionString)
{
var connection = new VistaDBConnection(connectionString);
connection.Open();
return(connection);
}
public School(string schoolName)
{
SchoolName = schoolName;
using (VistaDBConnection connection = Connection.Connexion)
{
try
{
connection.Open();
using (VistaDBCommand command = new VistaDBCommand())
{
command.Connection = connection;
command.CommandText = $"SELECT Id,TypeId FROM dbo.School WHERE SchoolName='{schoolName}'";
var reader = command.ExecuteReader();
while (reader.Read())
{
Id = reader.GetInt32(0);
TypeId = reader.GetInt32(1);
}
connection.Close();
}
}
catch (VistaDBException exception)
{
MessageBox.Show(exception.Message);
}
}
}
public int Role()
{
using (VistaDBConnection connection = Connection.Connexion)
{
try
{
connection.Open();
using (VistaDBCommand command = new VistaDBCommand())
{
command.Connection = connection;
command.CommandText = $"SELECT RoleId FROM dbo.Users WHERE Id={UserId}";
var reader = command.ExecuteReader();
while (reader.Read())
{
RoleId = reader.GetInt32(0);
}
connection.Close();
}
}
catch (VistaDBException exception)
{
MessageBox.Show("Something went wrong");
Log.Error(exception);
}
}
return(RoleId);
}
public DataTable ViewPending(string serch)
{
var data = new DataTable();
using (VistaDBConnection connection = new VistaDBConnection(new Connection().ConnectionString))
{
try
{
connection.Open();
using (VistaDBCommand command = new VistaDBCommand())
{
command.Connection = connection;
var query = command.CommandText = $"SELECT PoNumber as PONumber,UserId,Date FROM dbo.PaymentOrder where status='Pending'";
var adapter = new VistaDBDataAdapter(command.CommandText, command.Connection);
adapter.Fill(data);
connection.Close();
}
}
catch (VistaDBException exception)
{
MessageBox.Show("Something went wrong");
Log.Error(exception);
}
}
return(data);
}
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 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();
}
public double StandingOrder()
{
double Value = 0;
using (VistaDBConnection connection = new VistaDBConnection(new Connection().ConnectionString))
{
try
{
connection.Open();
using (VistaDBCommand command = new VistaDBCommand())
{
command.Connection = connection;
command.CommandText = $"SELECT dbo.StandingOrder('{SchoolName}')";
var reader = command.ExecuteReader();
while (reader.Read())
{
Value = reader.GetDouble(0);
}
connection.Close();
}
}
catch (VistaDBException exception)
{
MessageBox.Show("Something went wrong");
Log.Error(exception);
}
}
return(Value);
}
protected override IDbConnection CreateConnection()
{
VistaDBConnection conn = new VistaDBConnection(ConnectionString);
conn.Open();
// _serverVersion = Convert.ToInt32(conn.ServerVersion.Split('.')[0]);
return(conn);
}
protected override IDbConnection CreateConnection()
{
VistaDBConnection conn = new VistaDBConnection(ConnectionString);
conn.Open();
// _serverVersion = Convert.ToInt32(conn.ServerVersion.Split('.')[0]);
return conn;
}
public void Open()
{
try
{
Conn.Open();
}
catch (Exception)
{
throw;
}
}
public VistaDB(String connectionString)
{
co.ConnectionString = connectionString;
try
{
co.Open();
_lastError = "";
}
catch (Exception e)
{
_lastError = e.Message;
}
}
public static T add_ConnectionStringTester <T>(this API_VistaDB vistaDB, T control, Action afterConnect)
where T : Control
{
control.clear();
var connectionString = control.add_GroupBox("Connection String").add_TextArea();
var connectionStringSamples = connectionString.parent().insert_Left <Panel>(200).add_GroupBox("Sample Connection Strings")
.add_TreeView()
.afterSelect <string>((text) => connectionString.set_Text(text));
var connectPanel = connectionString.insert_Below <Panel>(200);
var button = connectPanel.insert_Above <Panel>(25).add_Button("Connect").fill();
var response = connectPanel.add_GroupBox("Response").add_TextArea();
button.onClick(() => {
try
{
var text = connectionString.get_Text();
vistaDB.ConnectionString = text;
response.set_Text("Connecting using: {0}".format(text));
var sqlConnection = new VistaDBConnection(text);
sqlConnection.Open();
response.set_Text("Connected ok");
afterConnect();
}
catch (Exception ex)
{
vistaDB.LastError = ex.Message;
response.set_Text("Error: {0}".format(ex.Message));
}
});
//connectionString.set_Text(@"Data Source=.\SQLExpress;Trusted_Connection=True");
var sampleConnectionStrings = new List <string>();
//from http://www.connectionstrings.com/sql-server-2005
sampleConnectionStrings.add(@"data source='C:\Program Files (x86)\Checkmarx\Checkmarx Application Server\CxDB.vdb3'")
.add(@"Data Source=.\SQLExpress;Trusted_Connection=True")
.add(@"Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI")
.add(@"Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;")
.add(@"Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;")
.add(@"Server=.\SQLExpress;AttachDbFilename=c:\mydbfile.mdf;Database=dbname; Trusted_Connection=Yes;")
.add(@"Server=.\SQLExpress;AttachDbFilename=|DataDirectory|mydbfile.mdf; Database=dbname;Trusted_Connection=Yes;")
.add(@"Data Source=.\SQLExpress;Integrated Security=true; AttachDbFilename=|DataDirectory|\mydb.mdf;User Instance=true;");
connectionStringSamples.add_Nodes(sampleConnectionStrings).selectFirst();
button.click();
return(control);
}
public static VistaDBConnection getOpenConnection(this API_VistaDB vistaDB)
{
"[API_VistaDB] Opening Connection".info();
try
{
var sqlConnection = new VistaDBConnection(vistaDB.ConnectionString);
sqlConnection.Open();
return(sqlConnection);
}
catch (Exception ex)
{
vistaDB.LastError = ex.Message;
"[executeNonQuery] {0}".error(ex.Message);
//ex.log();
}
return(null);
}
private void testButton_Click(object sender, EventArgs e)
{
using (VistaDBConnection connection = new VistaDBConnection(ConnectionString))
{
try
{
connection.Open();
MessageBox.Show(this, "Connection Test Was Successful.",
"Connection Test", MessageBoxButtons.OK, MessageBoxIcon.Information);
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show(this, "Connection Test Failed." + Environment.NewLine + Environment.NewLine + ex.GetBaseException().Message,
"Connection Test", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
DataTable IMyMetaPlugin.GetViews(string database)
{
DataTable metaData = new DataTable();
//IVistaDBDatabase db = null;
try
{
metaData = context.CreateViewsDataTable();
using (VistaDBConnection conn = new VistaDBConnection())
{
conn.ConnectionString = context.ConnectionString;
conn.Open();
using (VistaDBCommand cmd = new VistaDBCommand("SELECT * FROM GetViews()", conn))
{
using (VistaDBDataAdapter da = new VistaDBDataAdapter(cmd))
{
DataTable views = new DataTable();
da.Fill(views);
foreach (DataRow vistaRow in views.Rows)
{
DataRow row = metaData.NewRow();
metaData.Rows.Add(row);
row["TABLE_NAME"] = vistaRow["VIEW_NAME"];
row["DESCRIPTION"] = vistaRow["DESCRIPTION"];
row["VIEW_TEXT"] = vistaRow["VIEW_DEFINITION"];
row["IS_UPDATABLE"] = vistaRow["IS_UPDATABLE"];
}
}
}
}
}
catch {}
return(metaData);
}
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 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 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 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 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 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);
}
}
}
/// <summary>
/// Logs an error to the database.
/// </summary>
/// <remarks>
/// Use the stored procedure called by this implementation to set a
/// policy on how long errors are kept in the log. The default
/// implementation stores all errors for an indefinite time.
/// </remarks>
public override string Log(Error error)
{
if (error == null)
{
throw new ArgumentNullException("error");
}
string errorXml = ErrorXml.EncodeString(error);
using (VistaDBConnection connection = new VistaDBConnection(this.ConnectionString))
using (VistaDBCommand command = connection.CreateCommand())
{
connection.Open();
command.CommandText = @"INSERT INTO ELMAH_Error
(Application, Host, Type, Source,
Message, [User], AllXml, StatusCode, TimeUtc)
VALUES
(@Application, @Host, @Type, @Source,
@Message, @User, @AllXml, @StatusCode, @TimeUtc);
SELECT @@IDENTITY";
command.CommandType = CommandType.Text;
VistaDBParameterCollection parameters = command.Parameters;
parameters.Add("@Application", VistaDBType.NVarChar, _maxAppNameLength).Value = ApplicationName;
parameters.Add("@Host", VistaDBType.NVarChar, 30).Value = error.HostName;
parameters.Add("@Type", VistaDBType.NVarChar, 100).Value = error.Type;
parameters.Add("@Source", VistaDBType.NVarChar, 60).Value = error.Source;
parameters.Add("@Message", VistaDBType.NVarChar, 500).Value = error.Message;
parameters.Add("@User", VistaDBType.NVarChar, 50).Value = error.User;
parameters.Add("@AllXml", VistaDBType.NText).Value = errorXml;
parameters.Add("@StatusCode", VistaDBType.Int).Value = error.StatusCode;
parameters.Add("@TimeUtc", VistaDBType.DateTime).Value = error.Time.ToUniversalTime();
return(Convert.ToString(command.ExecuteScalar(), CultureInfo.InvariantCulture));
}
}
public void CreateStoredProcedures()
{
try
{
using (var cn = new VistaDBConnection(Properties.Settings.Default.ConnectionString))
{
cn.Open();
using (var cmd = cn.CreateCommand())
{
foreach (
var sql in
Regex.Split(Properties.Resources.DatabaseReset, @"^\s*GO\s*$", RegexOptions.Multiline))
{
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
}
}
}
catch (Exception ex)
{
Trace.WriteLine(ex.Message);
}
}
public static bool Valid(string username, string password)
{
int user = 0;
bool valid = false;
using (VistaDBConnection connection = Connection.Connexion)
{
try
{
connection.Open();
using (VistaDBCommand command = new VistaDBCommand())
{
command.Connection = connection;
command.CommandText = $"SELECT UserId FROM dbo.Login WHERE UserName='******' AND Password='******'";
var reader = command.ExecuteReader();
while (reader.Read())
{
user++;
UserId = reader.GetInt32(0);
}
connection.Close();
if (user == 1)
{
valid = true;
}
}
}
catch (VistaDBException exception)
{
MessageBox.Show("Something went wrong");
Log.Error(exception);
}
}
return(valid);
}
/// <summary>
/// This method opens (if necessary) and assigns a connection, transaction, command type and parameters
/// to the provided command
/// </summary>
/// <param name="command">The VistaDBCommand to be prepared</param>
/// <param name="connection">A valid VistaDBConnection, on which to execute this command</param>
/// <param name="transaction">A valid VistaDBTransaction, or 'null'</param>
/// <param name="commandType">The CommandType (TableDirect, Text)</param>
/// <param name="commandText">The T-SQL command</param>
/// <param name="commandParameters">An array of VistaDBParameters to be associated with the command or 'null' if no parameters are required</param>
/// <param name="mustCloseConnection"><c>true</c> if the connection was opened by the method, otherwose is false.</param>
private static void PrepareCommand(VistaDBCommand command, VistaDBConnection connection, VistaDBTransaction transaction, CommandType commandType, string commandText, VistaDBParameter[] commandParameters, out bool mustCloseConnection )
{
if( command == null ) throw new ArgumentNullException( "command" );
if(commandType == CommandType.StoredProcedure ) throw new ArgumentException("Stored Procedures are not supported.");
// If the provided connection is not open, we will open it
if (connection.State != ConnectionState.Open)
{
mustCloseConnection = true;
connection.Open();
}
else
{
mustCloseConnection = false;
}
// Associate the connection with the command
command.Connection = connection;
// Set the command text (SQL statement)
command.CommandText = commandText;
// If we were provided a transaction, assign it
if (transaction != null)
{
if( transaction.Connection == null ) throw new ArgumentException( "The transaction was rollbacked or commited, please provide an open transaction.", "transaction" );
command.Transaction = transaction;
}
// Set the command type
command.CommandType = commandType;
// Attach the command parameters if they are provided
if (commandParameters != null)
{
AttachParameters(command, commandParameters);
}
return;
}
public static int GetDatabaseVersionProcedure(out string versionString )
{
try
{
//To open a SQL connection to VistaDB from within a CLR Proc you must set the connection string to Context connection=true like this....
//NOTE: We DO want to dispose of this object because we are the ones allocating it
using (VistaDBConnection conn = new VistaDBConnection("Context Connection=true"))
{
conn.Open();
using (VistaDBCommand command = new VistaDBCommand())
{
command.Connection = conn;
command.CommandText = "SELECT @@Version";
versionString = Convert.ToString(command.ExecuteScalar());
return 0;
}
}
}
catch (Exception e)
{
throw new ApplicationException("Unable to get the database version due to Application Error", e);
}
}
public static string GetDatabaseVersionFunction()
{
try
{
//To open a SQL connection to VistaDB from within a CLR Proc you must set the connection string to Context connection=true like this....
//NOTE: We DO want to dispose of this object because we are the ones allocating it
using (VistaDBConnection conn = new VistaDBConnection("Context Connection=true"))
{
conn.Open();
using (VistaDBCommand command = new VistaDBCommand())
{
command.Connection = conn;
command.CommandText = "SELECT @@Version";
return Convert.ToString(command.ExecuteScalar());
}
}
}
catch (Exception e)
{
return e.Message;
}
}
DataTable IMyMetaPlugin.GetViewColumns(string database, string view)
{
DataTable metaData = new DataTable();
//IVistaDBDatabase db = null;
try
{
metaData = context.CreateColumnsDataTable();
using (VistaDBConnection conn = new VistaDBConnection())
{
conn.ConnectionString = context.ConnectionString;
conn.Open();
string sql = "SELECT * FROM GetViewColumns('" + view + "')";
using (VistaDBCommand cmd = new VistaDBCommand(sql, conn))
{
using (VistaDBDataAdapter da = new VistaDBDataAdapter(cmd))
{
DataTable views = new DataTable();
da.Fill(views);
foreach(DataRow vistaRow in views.Rows)
{
DataRow row = metaData.NewRow();
metaData.Rows.Add(row);
int width = Convert.ToInt32(vistaRow["COLUMN_SIZE"]);
int dec = 0;
int length = 0;
int octLength = width;
bool timestamp = false;
string type = vistaRow["DATA_TYPE_NAME"] as string;
switch(type)
{
case "Char":
case "NChar":
case "NText":
case "NVarchar":
case "Text":
case "Varchar":
length = width;
width = 0;
dec = 0;
break;
case "Currency":
case "Double":
case "Decimal":
case "Single":
break;
case "Timestamp":
timestamp = true;
break;
default:
width = 0;
dec = 0;
break;
}
string def = Convert.ToString(vistaRow["DEFAULT_VALUE"]);
row["TABLE_NAME"] = view;
row["COLUMN_NAME"] = vistaRow["COLUMN_NAME"];
row["ORDINAL_POSITION"] = vistaRow["COLUMN_ORDINAL"];
row["IS_NULLABLE"] = vistaRow["ALLOW_NULL"];
row["COLUMN_HASDEFAULT"] = def == string.Empty ? false : true;
row["COLUMN_DEFAULT"] = def;
row["IS_AUTO_KEY"] = vistaRow["IDENTITY_VALUE"];
row["AUTO_KEY_SEED"] = vistaRow["IDENTITY_SEED"];
row["AUTO_KEY_INCREMENT"] = vistaRow["IDENTITY_STEP"];
row["TYPE_NAME"] = type;
row["NUMERIC_PRECISION"] = width;
row["NUMERIC_SCALE"] = dec;
row["CHARACTER_MAXIMUM_LENGTH"] = length;
row["CHARACTER_OCTET_LENGTH"] = octLength;
row["DESCRIPTION"] = vistaRow["COLUMN_DESCRIPTION"];
if (timestamp)
{
row["IS_COMPUTED"] = true;
}
}
}
}
}
}
catch{}
return metaData;
}
DataTable IMyMetaPlugin.GetViews(string database)
{
DataTable metaData = new DataTable();
//IVistaDBDatabase db = null;
try
{
metaData = context.CreateViewsDataTable();
using (VistaDBConnection conn = new VistaDBConnection())
{
conn.ConnectionString = context.ConnectionString;
conn.Open();
using (VistaDBCommand cmd = new VistaDBCommand("SELECT * FROM GetViews()", conn))
{
using (VistaDBDataAdapter da = new VistaDBDataAdapter(cmd))
{
DataTable views = new DataTable();
da.Fill(views);
foreach(DataRow vistaRow in views.Rows)
{
DataRow row = metaData.NewRow();
metaData.Rows.Add(row);
row["TABLE_NAME"] = vistaRow["VIEW_NAME"];
row["DESCRIPTION"] = vistaRow["DESCRIPTION"];
row["VIEW_TEXT"] = vistaRow["VIEW_DEFINITION"];
row["IS_UPDATABLE"] = vistaRow["IS_UPDATABLE"];
}
}
}
}
}
catch{}
return metaData;
}
/// <summary>
/// Returns the specified error from the database, or null
/// if it does not exist.
/// </summary>
public override ErrorLogEntry GetError(string id)
{
if (id == null)
throw new ArgumentNullException("id");
if (id.Length == 0)
throw new ArgumentException(null, "id");
int errorId;
try
{
errorId = int.Parse(id, CultureInfo.InvariantCulture);
}
catch (FormatException e)
{
throw new ArgumentException(e.Message, "id", e);
}
catch (OverflowException e)
{
throw new ArgumentException(e.Message, "id", e);
}
string errorXml;
using (VistaDBConnection connection = new VistaDBConnection(this.ConnectionString))
using (VistaDBCommand command = connection.CreateCommand())
{
command.CommandText = @"SELECT AllXml
FROM ELMAH_Error
WHERE ErrorId = @ErrorId";
command.CommandType = CommandType.Text;
VistaDBParameterCollection parameters = command.Parameters;
parameters.Add("@ErrorId", VistaDBType.Int).Value = errorId;
connection.Open();
// NB this has been deliberately done like this as command.ExecuteScalar
// is not exhibiting the expected behaviour in VistaDB at the moment
using (VistaDBDataReader dr = command.ExecuteReader())
{
if (dr.Read())
errorXml = dr[0] as string;
else
errorXml = null;
}
}
if (errorXml == null)
return null;
Error error = ErrorXml.DecodeString(errorXml);
return new ErrorLogEntry(this, id, error);
}
/// <summary>
/// Logs an error to the database.
/// </summary>
/// <remarks>
/// Use the stored procedure called by this implementation to set a
/// policy on how long errors are kept in the log. The default
/// implementation stores all errors for an indefinite time.
/// </remarks>
public override string Log(Error error)
{
if (error == null)
throw new ArgumentNullException("error");
string errorXml = ErrorXml.EncodeString(error);
using (VistaDBConnection connection = new VistaDBConnection(this.ConnectionString))
using (VistaDBCommand command = connection.CreateCommand())
{
connection.Open();
command.CommandText = @"INSERT INTO ELMAH_Error
(Application, Host, Type, Source,
Message, [User], AllXml, StatusCode, TimeUtc)
VALUES
(@Application, @Host, @Type, @Source,
@Message, @User, @AllXml, @StatusCode, @TimeUtc);
SELECT @@IDENTITY";
command.CommandType = CommandType.Text;
VistaDBParameterCollection parameters = command.Parameters;
parameters.Add("@Application", VistaDBType.NVarChar, _maxAppNameLength).Value = ApplicationName;
parameters.Add("@Host", VistaDBType.NVarChar, 30).Value = error.HostName;
parameters.Add("@Type", VistaDBType.NVarChar, 100).Value = error.Type;
parameters.Add("@Source", VistaDBType.NVarChar, 60).Value = error.Source;
parameters.Add("@Message", VistaDBType.NVarChar, 500).Value = error.Message;
parameters.Add("@User", VistaDBType.NVarChar, 50).Value = error.User;
parameters.Add("@AllXml", VistaDBType.NText).Value = errorXml;
parameters.Add("@StatusCode", VistaDBType.Int).Value = error.StatusCode;
parameters.Add("@TimeUtc", VistaDBType.DateTime).Value = error.Time.ToUniversalTime();
return Convert.ToString(command.ExecuteScalar(), CultureInfo.InvariantCulture);
}
}
/// <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);
}
}
}