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())); }