public DbTransaction[] GetTransactions(int walletId)
{
using (var dataTable = ExecuteStoredProcedure("[dbo].[sp_SelectTransactions]", ("@walletId", SqlDbType.Int, string.Empty, walletId)))
{
var result = Map <DbTransaction>(dataTable);
return(result.ToArray());
}
}
public bool InsertOrUpdateTransactions(int walletId, DbTransactionParam[] transactions)
{
using (var transactionsTable = ToDataTable())
{
try
{
using (ExecuteStoredProcedure(
IsolationLevel.Serializable,
"[dbo].[sp_InsertOrUpdateTransactions]",
("@walletId", SqlDbType.Int, string.Empty, walletId),
("@transactions", SqlDbType.Structured, "dbo.type_Transaction", transactionsTable)))
{
return(true);
}
}
catch (Exception ex)
{
do
{
if (ex.Message.Contains("UQ_tbl_Transaction_TxId"))
{
return(false);
}
ex = ex.InnerException;
} while (ex != null);
throw;
}
}
DataTable ToDataTable()
{
var dataTable = new DataTable();
dataTable.Columns.Add(nameof(DbTransactionParam.Id), typeof(int));
dataTable.Columns.Add(nameof(DbTransactionParam.TxId), typeof(string));
dataTable.Columns.Add(nameof(DbTransactionParam.Amount), typeof(decimal));
dataTable.Columns.Add(nameof(DbTransactionParam.Fee), typeof(decimal));
dataTable.Columns.Add(nameof(DbTransactionParam.Category), typeof(bool));
dataTable.Columns.Add(nameof(DbTransactionParam.Address), typeof(string));
dataTable.Columns.Add(nameof(DbTransactionParam.ReceivedDateTime), typeof(DateTime));
dataTable.Columns.Add(nameof(DbTransactionParam.Confirmations), typeof(int));
foreach (var transaction in transactions)
{
dataTable.Rows.Add(
transaction.Id,
transaction.TxId,
transaction.Amount,
transaction.Fee,
transaction.Category,
transaction.Address,
transaction.ReceivedDateTime,
transaction.Confirmations);
}
return(dataTable);
}
}
private List <UserDefProcedures> GetUserDefViews(string DBName)
{
ExecuteStoredProcedure _esp = new ExecuteStoredProcedure();
List <UserDefProcedures> _UserDefViews = new List <UserDefProcedures>();
string CommandText =
"SELECT "
+ " Object_id, "
+ " Name "
+ "FROM sys.views "
+ "WHERE is_ms_shipped = 0 "
+ "ORDER BY Name ";
SqlConnection conn = GetDBConnection(DBName);
if (conn == null)
{
return(null);
}
List <UserDefProcedures> _UserDefStProcedures = new List <UserDefProcedures>();
DataSet UserDefViews = _esp.ExecuteSQLTextDS(CommandText, conn, "UserDefViews");
_UserDefViews = Db.FillGenObjectDS <UserDefProcedures, DataSet>(new UserDefProcedures(), UserDefViews);
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
return(_UserDefViews);
}
public void List_TableColumns(TreeNode Table)
{
ExecuteStoredProcedure _esp = new ExecuteStoredProcedure();
SqlConnection conn;
string TableName = Table.Parent.Text;
string CommandText =
"SELECT DISTINCT"
+ " so.Name AS TableName, " // TableName
+ " sc.Name AS ColumnName, " // Columns
+ " st.name AS DataType, " // Data Type
+ " sc.[Length] AS Data_Length " // Data Lengths
+ "FROM sys.sysobjects AS SO "
+ "JOIN sys.syscolumns AS SC "
+ " ON so.ID = SC.ID "
+ "JOIN sys.systypes AS ST "
+ " ON sc.xtype = st.xtype "
+ "WHERE sc.ID = OBJECT_ID('" + TableName + "')"
+ " AND st.Name != 'sysname';";
List <TableColumnInfo> _TableColumns = new List <TableColumnInfo>();
conn = GetDBConnection(SelectedDB, true);
DataSet TableColumns = _esp.ExecuteSQLTextDS(CommandText, conn, "TableColumns");
_TableColumns = Db.FillGenObjectDS <TableColumnInfo, DataSet>(new TableColumnInfo(), TableColumns);
foreach (TableColumnInfo Column in _TableColumns)
{
Table.Nodes.Add(Column.ColumnName);
Debug.WriteLine(Column.ColumnName);
}
}
private List <UserDefViews> GetUserDefViews(string DBName)
{
ExecuteStoredProcedure _esp = new ExecuteStoredProcedure();
SqlConnection conn; // = GetDBConnection(DBName);
string CommandText =
"SELECT "
+ " Object_id, "
+ " Name "
+ "FROM sys.views "
+ "WHERE is_ms_shipped = 0 "
+ "ORDER BY Name ";
//if (!(SelectedDatabaseInstance == null))
//{
// conn = SelectedDatabaseInstance.ConnectionContext.SqlConnectionObject;
//}
//else
//{
conn = GetDBConnection(DBName, true);
//}
if (conn == null)
{
return(null);
}
List <UserDefViews> _UserDefViews = new List <UserDefViews>();
DataSet UserDefViewsDS = _esp.ExecuteSQLTextDS(CommandText, conn, "UserDefViews");
_UserDefViews = Db.FillGenObjectDS <UserDefViews, DataSet>(new UserDefViews(), UserDefViewsDS);
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
return(_UserDefViews);
}
public string GetRowCount(string TableName)
{
ExecuteStoredProcedure _esp = new ExecuteStoredProcedure();
List <TableRowCount> _TableRowCount = new List <TableRowCount>();
string CommandText =
"SELECT "
+ " SCHEMA_NAME(schema_id) AS [SchemaName], "
+ " [Tables].name AS [TableName], "
+ " CAST(SUM([Partitions].[rows]) AS INT) AS [TotalRowCount] "
+ "FROM sys.tables AS [Tables] "
+ " JOIN sys.partitions AS [Partitions] "
+ " ON [Tables].[object_id] = [Partitions].[object_id] "
+ " AND [Partitions].index_id IN (0, 1) "
+ "WHERE [Tables].name = '" + TableName + "' "
+ "GROUP BY "
+ " SCHEMA_NAME(schema_id), "
+ " [Tables].name";
SqlConnection conn = GetDBConnection(SelectedDB, true);
if (conn == null)
{
return(null);
}
DataSet DS_TableRowCount = _esp.ExecuteSQLTextDS(CommandText, conn, "TableRowCount");
_TableRowCount = Db.FillGenObjectDS <TableRowCount, DataSet>(new TableRowCount(), DS_TableRowCount);
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
return(string.Format("Table: {0} Rows: {1}", _TableRowCount[0].TableName.ToString(), _TableRowCount[0].TotalRowCount.ToString()));
}
