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);
}
private void SetConnection(IDbConnection value)
{
if (_connection != value)
{
_connection = (VistaDBConnection)value;
}
}
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);
}
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);
}
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);
}
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 ViewSchema[] GetViews(string connectionString, DatabaseSchema database)
{
var views = new List <ViewSchema>();
var extendedProperties = new List <ExtendedProperty>();
const string sql = "SELECT VIEW_NAME, DESCRIPTION, IS_UPDATABLE FROM GetViews()";
using (VistaDBConnection connection = GetConnection(connectionString))
using (var adapter = new VistaDBDataAdapter(sql, connection))
using (var table = new DataTable())
{
adapter.Fill(table);
foreach (DataRow row in table.Rows)
{
string name = row[0].ToString();
string description = row[1].ToString();
bool isUpdatable = (bool)(row[2] ?? true);
extendedProperties.Clear();
extendedProperties.Add(ExtendedProperty.Readonly(ExtendedPropertyNames.Description, description));
extendedProperties.Add(ExtendedProperty.Readonly("CS_IsUpdatable", isUpdatable));
var view = new ViewSchema(database, name, string.Empty, DateTime.MinValue, extendedProperties.ToArray());
views.Add(view);
}
}
return(views.ToArray());
}
DataTable IPlugin.GetViews(string database)
{
DataTable metaData = new DataTable();
try
{
metaData = context.CreateViewsDataTable();
using (VistaDBConnection conn = new VistaDBConnection(context.ConnectionString))
{
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>
/// 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);
}
}
}
/// <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 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 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 ViewColumnSchema[] GetViewColumns(string connectionString, ViewSchema view)
{
var columns = new List <ViewColumnSchema>();
string sql = string.Format("SELECT * FROM GetViewColumns('{0}')", view.Name);
using (VistaDBConnection connection = GetConnection(connectionString))
using (var adapter = new VistaDBDataAdapter(sql, connection))
using (var table = new DataTable())
{
adapter.Fill(table);
foreach (DataRow row in table.Rows)
{
string name = row["COLUMN_NAME"].ToString();
string nativeType = row["DATA_TYPE_NAME"].ToString();
var size = (int)(row["COLUMN_SIZE"] ?? 0);
var allowNull = (bool)(row["ALLOW_NULL"] ?? true);
var column = new ViewColumnSchema(
view, name, GetDbType(nativeType),
nativeType, size, 0, 0, allowNull);
columns.Add(column);
}
}
return(columns.ToArray());
}
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 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 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 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 Table[] GetMetaData(string server, string database, string login, string password, string fn)
{
List<Table> tables = new List<Table>();
IVistaDBDatabase vistaDB = VistaDBEngine.Connections.OpenDDA().OpenDatabase(fn, VistaDBDatabaseOpenMode.NonexclusiveReadWrite, null);
string[] tableNames = (string[])vistaDB.EnumTables().ToArray(typeof(string));
vistaDB.Close();
var conn = new VistaDBConnection("Data Source=" + fn);
foreach (string tableName in tableNames)
{
string className = tableName;
if (tableName.StartsWith("tbl"))
className = tableName.Substring(3);
tables.Add(GetTable(conn, tableName, className));
}
conn.Close();
return tables.ToArray();
}
public IEnumerable <long> GetQualified(long qualifier)
{
List <long> qualifiedRelations = new List <long>();
using (VistaDBConnection conn = new VistaDBConnection(this.db))
{
VistaDBCommand cmd;
// get qualified root relations
cmd = new VistaDBCommand("select id from RootRelations where qualifierId=@qualifierId", conn);
cmd.Parameters.AddWithValue("@qualifierId", qualifier);
using (VistaDBDataReader rd = cmd.ExecuteReader())
{
while (rd.Read())
{
qualifiedRelations.Add(rd.GetInt64(0));
}
}
// get qualified inner relations
cmd.CommandText = "select id from InnerRelations where qualifierId=@qualifierId";
using (VistaDBDataReader rd = cmd.ExecuteReader())
{
while (rd.Read())
{
qualifiedRelations.Add(rd.GetInt64(0));
}
}
}
return(qualifiedRelations);
}
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 CommandSchema[] GetCommands(string connectionString, DatabaseSchema database)
{
var commands = new List <CommandSchema>();
var extendedProperties = new List <ExtendedProperty>();
const string sql = "SELECT PROC_NAME, PROC_DESCRIPTION FROM sp_stored_procedures()";
using (VistaDBConnection connection = GetConnection(connectionString))
using (var adapter = new VistaDBDataAdapter(sql, connection))
using (var table = new DataTable())
{
adapter.Fill(table);
foreach (DataRow row in table.Rows)
{
string name = row[0].ToString();
string description = row[1].ToString();
extendedProperties.Clear();
extendedProperties.Add(ExtendedProperty.Readonly(ExtendedPropertyNames.Description, description));
var command = new CommandSchema(database, name, string.Empty, DateTime.MinValue, extendedProperties.ToArray());
commands.Add(command);
}
}
return(commands.ToArray());
}
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);
}
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;
}
private string GetPassword(VistaDBConnection conn)
{
string password = null;
if (conn.Password != null && conn.Password.Length > 0)
{
password = conn.Password;
}
return(password);
}
public DataTable GetViewData(string connectionString, ViewSchema view)
{
string sql = string.Format("select * from '{0}'", view.Name);
using (VistaDBConnection connection = GetConnection(connectionString))
using (var adapter = new VistaDBDataAdapter(sql, connection))
using (var dataTable = new DataTable())
{
adapter.Fill(dataTable);
return(dataTable);
}
}
public string GetDatabaseName()
{
VistaDBConnection cn = new VistaDBConnection(this.context.ConnectionString);
string dbName = cn.DataSource;
int index = dbName.LastIndexOfAny(new char[] { '\\' });
if (index >= 0)
{
dbName = dbName.Substring(index + 1);
}
return(dbName);
}
List <string> IPlugin.GetPrimaryKeyColumns(string database, string table)
{
List <string> primaryKeys = new List <string>();
IVistaDBDatabase db = null;
try
{
using (VistaDBConnection cn = new VistaDBConnection(context.ConnectionString))
{
db = DDA.OpenDatabase(this.GetFullDatabaseName(), VistaDBDatabaseOpenMode.NonexclusiveReadOnly, GetPassword(cn));
}
IVistaDBTableSchema tblStructure = db.TableSchema(table);
string[] pks = null;
if (tblStructure.Indexes.Contains("PrimaryKey"))
{
pks = tblStructure.Indexes["PrimaryKey"].KeyExpression.Split(';');
}
else
{
foreach (IVistaDBIndexInformation pk in tblStructure.Indexes)
{
if (pk.Primary)
{
pks = pk.KeyExpression.Split(';');
break;
}
}
}
if (pks != null)
{
foreach (string pkColName in pks)
{
primaryKeys.Add(pkColName);
}
}
}
finally
{
if (db != null)
{
db.Close();
}
}
return(primaryKeys);
}
/// <summary>
/// Simplify the creation of a VistaDB command object by allowing
/// a CommandType and Command Text to be provided
/// </summary>
/// <remarks>
/// e.g.:
/// VistaDBCommand command = CreateCommand(conn, CommandType.Text, "Select * from Customers");
/// </remarks>
/// <param name="connection">A valid VistaDBConnection object</param>
/// <param name="commandType">CommandType (TableDirect, Text)</param>
/// <param name="commandText">CommandText</param>
/// <returns>A valid VistaDBCommand object</returns>
public static VistaDBCommand CreateCommand(VistaDBConnection connection, CommandType commandType, string commandText )
{
if( connection == null ) throw new ArgumentNullException( "connection" );
if( commandType == CommandType.StoredProcedure ) throw new ArgumentException("Stored Procedures are not supported.");
// If we receive parameter values, we need to figure out where they go
if ((commandText == null) && (commandText.Length<= 0)) throw new ArgumentNullException( "Command Text" );
// Create a VistaDBCommand
VistaDBCommand cmd = new VistaDBCommand(commandText, connection );
cmd.CommandType = CommandType.Text ;
return cmd;
}
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);
}
DataTable IPlugin.GetTableIndexes(string database, string table)
{
DataTable metaData = new DataTable();
IVistaDBDatabase db = null;
try
{
metaData = context.CreateIndexesDataTable();
using (VistaDBConnection cn = new VistaDBConnection(context.ConnectionString))
{
db = DDA.OpenDatabase(this.GetFullDatabaseName(), VistaDBDatabaseOpenMode.NonexclusiveReadOnly, GetPassword(cn));
}
ArrayList tables = db.EnumTables();
IVistaDBTableSchema tblStructure = db.TableSchema(table);
foreach (IVistaDBIndexInformation indexInfo in tblStructure.Indexes)
{
string[] pks = indexInfo.KeyExpression.Split(';');
int index = 0;
foreach (string colName in pks)
{
DataRow row = metaData.NewRow();
metaData.Rows.Add(row);
row["TABLE_CATALOG"] = GetDatabaseName();
row["TABLE_NAME"] = tblStructure.Name;
row["INDEX_CATALOG"] = GetDatabaseName();
row["INDEX_NAME"] = indexInfo.Name;
row["UNIQUE"] = indexInfo.Unique;
row["COLLATION"] = indexInfo.KeyStructure[index++].Descending ? 2 : 1;
row["COLUMN_NAME"] = colName;
}
}
}
finally
{
if (db != null)
{
db.Close();
}
}
return(metaData);
}
public static VistaDBConnection closeConnection(this API_VistaDB vistaDB, VistaDBConnection sqlConnection)
{
"[API_VistaDB] Closing Connection".info();
try
{
sqlConnection.Close();
return(sqlConnection);
}
catch (Exception ex)
{
vistaDB.LastError = ex.Message;
"[executeNonQuery] {0}".error(ex.Message);
//ex.log();
}
return(null);
}
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);
}
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 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 string GetFullDatabaseName()
{
VistaDBConnection cn = new VistaDBConnection(this.context.ConnectionString);
return cn.DataSource;
}
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;
}
public string GetDatabaseName()
{
VistaDBConnection cn = new VistaDBConnection(this.context.ConnectionString);
string dbName = cn.DataSource;
int index = dbName.LastIndexOfAny(new char[]{'\\'});
if (index >= 0)
{
dbName = dbName.Substring(index + 1);
}
return dbName;
}
DataTable IPlugin.GetForeignKeys(string database, string tableName)
{
DataTable metaData = new DataTable();
IVistaDBDatabase db = null;
try
{
metaData = context.CreateForeignKeysDataTable();
using (VistaDBConnection cn = new VistaDBConnection(context.ConnectionString))
{
db = DDA.OpenDatabase(this.GetFullDatabaseName(), VistaDBDatabaseOpenMode.NonexclusiveReadOnly, GetPassword(cn));
}
ArrayList tables = db.EnumTables();
foreach (string table in tables)
{
IVistaDBTableSchema tblStructure = db.TableSchema(table);
//==================================================================
// This works around a change that was made to the VistaDB provider
// It's ugly, we know
//==================================================================
IEnumerator enumerator = null;
if (useOldForeignKeyWay)
{
enumerator = tblStructure.ForeignKeys.GetEnumerator();
}
else
{
try
{
enumerator = tblStructure.ForeignKeys.Values.GetEnumerator();
}
catch
{
enumerator = tblStructure.ForeignKeys.GetEnumerator();
useOldForeignKeyWay = true;
}
}
// Okay, now that the version issues are over we just use the 'enumerator'
while(enumerator.MoveNext())
{
IVistaDBRelationshipInformation relInfo = enumerator.Current as IVistaDBRelationshipInformation;
if(relInfo.ForeignTable != tableName && relInfo.PrimaryTable != tableName)
continue;
string fCols = relInfo.ForeignKey;
string pCols = String.Empty;
string primaryTbl = relInfo.PrimaryTable;
string pkName = "";
using (IVistaDBTableSchema pkTableStruct = db.TableSchema(primaryTbl))
{
foreach (IVistaDBIndexInformation idxInfo in pkTableStruct.Indexes)
{
if (!idxInfo.Primary)
continue;
pkName = idxInfo.Name;
pCols = idxInfo.KeyExpression;
break;
}
}
string [] fColumns = fCols.Split(';');
string [] pColumns = pCols.Split(';');
for(int i = 0; i < fColumns.GetLength(0); i++)
{
DataRow row = metaData.NewRow();
metaData.Rows.Add(row);
row["PK_TABLE_CATALOG"] = GetDatabaseName();
row["PK_TABLE_SCHEMA"] = DBNull.Value;
row["FK_TABLE_CATALOG"] = DBNull.Value;
row["FK_TABLE_SCHEMA"] = DBNull.Value;
row["FK_TABLE_NAME"] = tblStructure.Name;
row["PK_TABLE_NAME"] = relInfo.PrimaryTable;
row["ORDINAL"] = 0;
row["FK_NAME"] = relInfo.Name;
row["PK_NAME"] = pkName;
row["PK_COLUMN_NAME"] = pColumns[i];
row["FK_COLUMN_NAME"] = fColumns[i];
row["UPDATE_RULE"] = relInfo.UpdateIntegrity;
row["DELETE_RULE"] = relInfo.DeleteIntegrity;
}
}
}
}
finally
{
if(db != null) db.Close();
}
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>
/// Execute a VistaDBCommand (that returns a resultset and takes no parameters) against the provided VistaDBConnection.
/// </summary>
/// <remarks>
/// e.g.:
/// FillDataset(conn, CommandType.Text, "Select * from TableTransaction", ds, new string[] {"orders"});
/// </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="dataSet">A dataset wich will contain the resultset generated by the command</param>
/// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
/// by a user defined name (probably the actual table name)
/// </param>
public static void FillDataset(VistaDBConnection connection, CommandType commandType,
string commandText, DataSet dataSet, string[] tableNames)
{
FillDataset(connection, commandType, commandText, dataSet, tableNames, null);
}
/// <summary>
/// Execute a VistaDBCommand (that returns a resultset) against the specified VistaDBConnection
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// string r = ExecuteXml(conn, CommandType.Text, "Select * from TableTransaction 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 using "FOR XML AUTO"</param>
/// <param name="commandParameters">An array of VistaDBParamters used to execute the command</param>
/// <returns>An string containing the resultset generated by the command</returns>
public static string ExecuteXml(VistaDBConnection connection, CommandType commandType, string commandText, params VistaDBParameter[] commandParameters)
{
if( connection == null ) throw new ArgumentNullException( "connection" );
bool mustCloseConnection = false;
// Create a command and prepare it for execution
VistaDBCommand cmd = new VistaDBCommand();
try
{
PrepareCommand(cmd, connection, (VistaDBTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection );
// Create the DataAdapter & DataSet
VistaDBDataAdapter obj_Adapter =new VistaDBDataAdapter (cmd);
DataSet ds=new DataSet();
ds.Locale =CultureInfo.InvariantCulture;
obj_Adapter.Fill(ds);
// Detach the VistaDBParameters from the command object, so they can be used again
cmd.Parameters.Clear();
string retval= ds.GetXml();
ds.Clear();
obj_Adapter.Dispose ();
return retval;
}
catch
{
if( mustCloseConnection )
connection.Close();
throw;
}
}
DataTable IPlugin.GetTables(string database)
{
DataTable metaData = new DataTable();
IVistaDBDatabase db = null;
try
{
metaData = context.CreateTablesDataTable();
using (VistaDBConnection cn = new VistaDBConnection(context.ConnectionString))
{
db = DDA.OpenDatabase(this.GetFullDatabaseName(), VistaDBDatabaseOpenMode.NonexclusiveReadOnly, GetPassword(cn));
}
ArrayList tables = db.EnumTables();
foreach (string table in tables)
{
IVistaDBTableSchema tblStructure = db.TableSchema(table);
DataRow row = metaData.NewRow();
metaData.Rows.Add(row);
row["TABLE_NAME"] = tblStructure.Name;
row["DESCRIPTION"] = tblStructure.Description;
}
}
finally
{
if (db != null) db.Close();
}
return metaData;
}
/// <summary>
/// Create and prepare a VistaDBCommand, and call ExecuteReader with the appropriate CommandBehavior.
/// </summary>
/// <remarks>
/// If we created and opened the connection, we want the connection to be closed when the DataReader is closed.
///
/// If the caller provided the connection, we want to leave it to them to manage.
/// </remarks>
/// <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="connectionOwnership">Indicates whether the connection parameter was provided by the caller, or created by VistaDBHelper</param>
/// <returns>VistaDBDataReader containing the results of the command</returns>
private static VistaDBDataReader ExecuteReader(VistaDBConnection connection, VistaDBTransaction transaction, CommandType commandType, string commandText, VistaDBParameter[] commandParameters, VistaDBConnectionOwnership connectionOwnership)
{
if( connection == null ) throw new ArgumentNullException( "connection" );
bool mustCloseConnection = false;
// Create a command and prepare it for execution
VistaDBCommand cmd = new VistaDBCommand();
try
{
PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection );
// Create a reader
VistaDBDataReader dataReader;
// Call ExecuteReader with the appropriate CommandBehavior
if (connectionOwnership == VistaDBConnectionOwnership.External)
{
dataReader = cmd.ExecuteReader();
}
else
{
dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
// Detach the VistaDBParameters from the command object, so they can be used again.
// HACK: There is a problem here, the output parameter values are fletched
// when the reader is closed, so if the parameters are detached from the command
// then the VistaDBReader can´t set its values.
// When this happen, the parameters can´t be used again in other command.
bool canClear = true;
foreach(VistaDBParameter commandParameter in cmd.Parameters)
{
if (commandParameter.Direction != ParameterDirection.Input)
canClear = false;
}
if (canClear)
{
cmd.Parameters.Clear();
}
return dataReader;
}
catch
{
if( mustCloseConnection )
connection.Close();
throw;
}
}
DataTable IPlugin.GetTableColumns(string database, string table)
{
DataTable metaData = new DataTable();
IVistaDBDatabase db = null;
try
{
metaData = context.CreateColumnsDataTable();
using (VistaDBConnection cn = new VistaDBConnection(context.ConnectionString))
{
db = DDA.OpenDatabase(this.GetFullDatabaseName(), VistaDBDatabaseOpenMode.NonexclusiveReadOnly, GetPassword(cn));
}
ArrayList tables = db.EnumTables();
IVistaDBTableSchema tblStructure = db.TableSchema(table);
foreach (IVistaDBColumnAttributes c in tblStructure)
{
string colName = c.Name;
string def = "";
if(tblStructure.Defaults.Contains(colName))
{
def = tblStructure.Defaults[colName].Expression;
}
int width = c.MaxLength; //c.ColumnWidth;
int dec = 0; //c.ColumnDecimals;
int length = 0;
int octLength = width;
IVistaDBIdentityInformation identity = null;
if(tblStructure.Identities.Contains(colName))
{
identity = tblStructure.Identities[colName];
}
string[] pks = null;
if(tblStructure.Indexes.Contains("PrimaryKey"))
{
pks = tblStructure.Indexes["PrimaryKey"].KeyExpression.Split(';');
}
else
{
foreach(IVistaDBIndexInformation pk in tblStructure.Indexes)
{
if(pk.Primary)
{
pks = pk.KeyExpression.Split(';');
break;
}
}
}
System.Collections.Hashtable pkCols = null;
if(pks != null)
{
pkCols = new Hashtable();
foreach(string pkColName in pks)
{
pkCols[pkColName] = true;
}
}
switch(c.Type)
{
case VistaDBType.Char:
case VistaDBType.NChar:
case VistaDBType.NText:
case VistaDBType.NVarChar:
case VistaDBType.Text:
case VistaDBType.VarChar:
length = width;
width = 0;
dec = 0;
break;
case VistaDBType.Money:
case VistaDBType.Float:
case VistaDBType.Decimal:
case VistaDBType.Real:
break;
default:
width = 0;
dec = 0;
break;
}
DataRow row = metaData.NewRow();
metaData.Rows.Add(row);
row["TABLE_NAME"] = tblStructure.Name;
row["COLUMN_NAME"] = c.Name;
row["ORDINAL_POSITION"] = c.RowIndex;
row["IS_NULLABLE"] = c.AllowNull;
row["COLUMN_HASDEFAULT"] = def == string.Empty ? false : true;
row["COLUMN_DEFAULT"] = def;
row["IS_AUTO_KEY"] = identity == null ? false : true;
row["AUTO_KEY_SEED"] = 1;
row["AUTO_KEY_INCREMENT"] = identity == null ? 0 : Convert.ToInt32(identity.StepExpression);
row["TYPE_NAME"] = c.Type.ToString();
row["NUMERIC_PRECISION"] = width;
row["NUMERIC_SCALE"] = dec;
row["CHARACTER_MAXIMUM_LENGTH"] = length;
row["CHARACTER_OCTET_LENGTH"] = octLength;
row["DESCRIPTION"] = c.Description;
string type = (string)row["TYPE_NAME"];
row["TYPE_NAME_COMPLETE"] = this.GetDataTypeNameComplete(type, length, (short)width, (short)dec);
if (c.Type == VistaDBType.Timestamp)
{
row["IS_COMPUTED"] = true;
}
row["IS_CONCURRENCY"] = type == "Timestamp" ? true : false;
}
}
finally
{
if(db != null) db.Close();
}
return metaData;
}
List<string> IPlugin.GetPrimaryKeyColumns(string database, string table)
{
List<string> primaryKeys = new List<string>();
IVistaDBDatabase db = null;
try
{
using (VistaDBConnection cn = new VistaDBConnection(context.ConnectionString))
{
db = DDA.OpenDatabase(this.GetFullDatabaseName(), VistaDBDatabaseOpenMode.NonexclusiveReadOnly, GetPassword(cn));
}
IVistaDBTableSchema tblStructure = db.TableSchema(table);
string[] pks = null;
if(tblStructure.Indexes.Contains("PrimaryKey"))
{
pks = tblStructure.Indexes["PrimaryKey"].KeyExpression.Split(';');
}
else
{
foreach(IVistaDBIndexInformation pk in tblStructure.Indexes)
{
if(pk.Primary)
{
pks = pk.KeyExpression.Split(';');
break;
}
}
}
if(pks != null)
{
foreach(string pkColName in pks)
{
primaryKeys.Add(pkColName);
}
}
}
finally
{
if(db != null) db.Close();
}
return primaryKeys;
}
DataTable IPlugin.GetTableIndexes(string database, string table)
{
DataTable metaData = new DataTable();
IVistaDBDatabase db = null;
try
{
metaData = context.CreateIndexesDataTable();
using (VistaDBConnection cn = new VistaDBConnection(context.ConnectionString))
{
db = DDA.OpenDatabase(this.GetFullDatabaseName(), VistaDBDatabaseOpenMode.NonexclusiveReadOnly, GetPassword(cn));
}
ArrayList tables = db.EnumTables();
IVistaDBTableSchema tblStructure = db.TableSchema(table);
foreach (IVistaDBIndexInformation indexInfo in tblStructure.Indexes)
{
string[] pks = indexInfo.KeyExpression.Split(';');
int index = 0;
foreach(string colName in pks)
{
DataRow row = metaData.NewRow();
metaData.Rows.Add(row);
row["TABLE_CATALOG"] = GetDatabaseName();
row["TABLE_NAME"] = tblStructure.Name;
row["INDEX_CATALOG"] = GetDatabaseName();
row["INDEX_NAME"] = indexInfo.Name;
row["UNIQUE"] = indexInfo.Unique;
row["COLLATION"] = indexInfo.KeyStructure[index++].Descending ? 2 : 1;
row["COLUMN_NAME"] = colName;
}
}
}
finally
{
if(db != null) db.Close();
}
return metaData;
}
/// <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);
}
}
}
/// <summary>
/// Execute a VistaDBCommand (that returns a 1x1 resultset) against the specified VistaDBConnection
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int orderCount = (int)ExecuteScalar(conn, CommandType.Text, "Select count(Order) from TableTransaction 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 object containing the value in the 1x1 resultset generated by the command</returns>
public static object ExecuteScalar(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 );
// Execute the command & return the results
object retval = cmd.ExecuteScalar();
// Detach the VistaDBParameters from the command object, so they can be used again
cmd.Parameters.Clear();
if( mustCloseConnection )
connection.Close();
return retval;
}
/// <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);
}
}
private string GetPassword(VistaDBConnection conn)
{
string password = null;
if (conn.Password != null && conn.Password.Length > 0)
{
password = conn.Password;
}
return password;
}
/// <summary>
/// Execute a VistaDBCommand (that returns a resultset) against the specified VistaDBConnection
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// FillDataset(conn, CommandType.Text, "Select * from TableTransaction where ProdId=?", ds, new string[] {"orders"}, 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="dataSet">A dataset wich will contain the resultset generated by the command</param>
/// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
/// by a user defined name (probably the actual table name)
/// </param>
/// <param name="commandParameters">An array of VistaDBParamters used to execute the command</param>
public static void FillDataset(VistaDBConnection connection, CommandType commandType,
string commandText, DataSet dataSet, string[] tableNames,
params VistaDBParameter[] commandParameters)
{
FillDataset(connection, null, commandType, commandText, dataSet, tableNames, commandParameters);
}
/// <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>
/// Execute a VistaDBCommand (that returns a resultset) against the database specified in the connection string
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// FillDataset(connString, CommandType.Text, "Select * from TableTransaction where ProdId=?", ds, new string[] {"orders"}, new VistaDBParameter("@prodid", 24));
/// </remarks>
/// <param name="connectionString">A valid connection string for a 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>
/// <param name="dataSet">A dataset wich will contain the resultset generated by the command</param>
/// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
/// by a user defined name (probably the actual table name)
/// </param>
public static void FillDataset(string connectionString, CommandType commandType,
string commandText, DataSet dataSet, string[] tableNames,
params VistaDBParameter[] commandParameters)
{
if( connectionString == null || connectionString.Length == 0 ) throw new ArgumentNullException( "connectionString" );
if( dataSet == null ) throw new ArgumentNullException( "dataSet" );
// Create & open a VistaDBConnection, and dispose of it after we are done
using (VistaDBConnection connection = new VistaDBConnection(connectionString))
{
// Call the overload that takes a connection in place of the connection string
FillDataset(connection, commandType, commandText, dataSet, tableNames, commandParameters);
}
}
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();
}
}
}
}
/// <summary>
/// Execute a VistaDBCommand (that returns a 1x1 resultset) against the database specified in the connection string
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int orderCount = (int)ExecuteScalar(connString, CommandType.Text, "Select count(Order) from TableTransaction where ProdId=?", new VistaDBParameter("@prodid", 24));
/// </remarks>
/// <param name="connectionString">A valid connection string for a 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 object containing the value in the 1x1 resultset generated by the command</returns>
public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText, params VistaDBParameter[] commandParameters)
{
if( connectionString == null || connectionString.Length == 0 ) throw new ArgumentNullException( "connectionString" );
// Create & open a VistaDBConnection, and dispose of it after we are done
using (VistaDBConnection connection = new VistaDBConnection(connectionString))
{
// Call the overload that takes a connection in place of the connection string
return ExecuteScalar(connection, commandType, commandText, commandParameters);
}
}
/// <summary>
/// Execute a VistaDBCommand (that returns a resultset and takes no parameters) against the provided VistaDBConnection.
/// </summary>
/// <remarks>
/// e.g.:
/// string r = ExecuteXml(conn, CommandType.Text, "Select * from TableTransaction");
/// </remarks>
/// <param name="connection">A valid VistaDBConnection</param>
/// <param name="commandType">The CommandType (TableDirect, Text)</param>
/// <param name="commandText">The T-SQL command using "FOR XML AUTO"</param>
/// <returns>An string containing the resultset generated by the command</returns>
public static string ExecuteXml(VistaDBConnection connection, CommandType commandType, string commandText)
{
// Pass through the call providing null for the set of VistaDBParameters
return ExecuteXml(connection, commandType, commandText, (VistaDBParameter[])null);
}
