private static void CreateMetaDataTable(SqlDatabaseConnection conn)
{
var cmd = new SqlDatabaseCommand
{
Connection = conn,
CommandText =
$@"CREATE TABLE IF NOT EXISTS [{Constants.SchemaName}].[{Constants.ImportMetaDataTableName}] (
{Constants.ImportMetaDataPathColumn} varchar(1024),
{Constants.ImportMetaDataLastModifiedDate} datetime
);"
};
cmd.ExecuteNonQuery(); // Create table
}
public static async Task <Dictionary <string, object> > GetRecordAsync(SqlDatabaseConnection conn,
ReplicationTable table,
string primaryKeyValue)
{
await conn.OpenAsync();
var query = string.Format(GetRecordQuery,
Utility.Utility.GetSafeName(table.SchemaName),
Utility.Utility.GetSafeName(table.TableName),
Utility.Utility.GetSafeName(table.Columns.Find(c => c.PrimaryKey == true).ColumnName),
primaryKeyValue
);
var cmd = new SqlDatabaseCommand
{
Connection = conn,
CommandText = query
};
var reader = await cmd.ExecuteReaderAsync();
Dictionary <string, object> recordMap = null;
// check if record exists
if (reader.HasRows)
{
await reader.ReadAsync();
recordMap = new Dictionary <string, object>();
foreach (var column in table.Columns)
{
try
{
recordMap[column.ColumnName] = reader[column.ColumnName];
}
catch (Exception e)
{
Logger.Error(e, $"No column with column name: {column.ColumnName}");
Logger.Error(e, e.Message);
recordMap[column.ColumnName] = null;
}
}
}
await conn.CloseAsync();
return(recordMap);
}
public void InvokeCommand_Throws_When_ResultEntityDelegate_Returns_Null()
{
// Arrange
var command = new SqlDatabaseCommand("INSERT INTO ...", DatabaseCommandType.Text, DatabaseOperation.Insert);
ProviderMock.SetupGet(x => x.ResultEntityDelegate).Returns((_, _) => null !);
// Act
Sut.Invoking(x => x.ExecuteCommand(command, new MyEntity {
Property = "filled"
}))
.Should().Throw <InvalidOperationException>()
.WithMessage("Instance should be supplied, or result entity delegate should deliver an instance");
}
public void InvokeCommand_AfterReadDelegate_Throws_When_ExecuteReader_Read_Returns_False()
{
// Arrange
var command = new SqlDatabaseCommand("INSERT INTO ...", DatabaseCommandType.Text, DatabaseOperation.Insert);
ProviderMock.SetupGet(x => x.AfterReadDelegate).Returns(new Func <MyEntity, DatabaseOperation, IDataReader, MyEntity>((x, _, _) => x));
// Act
Sut.Invoking(x => x.ExecuteCommand(command, new MyEntity {
Property = "test"
}).HandleResult("MyEntity entity was not added"))
.Should().Throw <DataException>()
.WithMessage("MyEntity entity was not added");
}
private void toolStripButton1_Click(object sender, EventArgs e)
{
if (cbConnections.SelectedItem == null)
{
return;
}
var conItem = cbConnections.SelectedItem as DbSettingItem;
var sql = Sql;
if (string.IsNullOrEmpty(sql))
{
return;
}
using (var con = conItem.GetConnection())
{
con.Open();
var cmd = new SqlDatabaseCommand(sql, con);
try
{
if (sql.ToUpper().StartsWith("SELECT "))
{
dgvResult.Visible = true;
lblResult.Visible = false;
var dt = new DataTable("Result");
using (var da = new SqlDatabaseDataAdapter(cmd))
{
da.Fill(dt);
}
dgvResult.DataSource = dt;
}
else
{
dgvResult.Visible = false;
lblResult.Visible = true;
var i = cmd.ExecuteNonQuery();
lblResult.Text = string.Format("{0} Rows affected", i);
}
}
catch (Exception ex)
{
dgvResult.Visible = false;
lblResult.Visible = true;
lblResult.Text = ex.Message + "\n\nQuery:\n" + sql;
}
}
}
private void btnCreate_Click(object sender, EventArgs e)
{
var table = cbTable.SelectedItem as SqlDataTable;
if (table == null)
{
MessageBox.Show("No table selected.", "Information", MessageBoxButtons.OK);
return;
}
var cols = new List <string>();
foreach (SqlDataColumn col in selectedColumns)
{
cols.Add(col.Name);
}
if (cols.Count == 0)
{
MessageBox.Show("No columns selected.", "Information", MessageBoxButtons.OK);
return;
}
const string Sql = "CREATE {0} INDEX IF NOT EXISTS {1} ON {2} ({3});";
string sql = string.Format(Sql, (Unique ? "UNIQUE" : ""), IndexName, table.Name, string.Join(",", cols));
using (var con = new SqlDatabaseConnection(table.ConnectionString))
{
try
{
con.Open();
using (var cmd = new SqlDatabaseCommand(sql, con))
{
cmd.ExecuteNonQuery();
}
this.DialogResult = DialogResult.OK;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
con.Close();
}
}
}
private void CreateSettingsTables(SqlDatabaseConnection con)
{
const string sql = "CREATE TABLE IF NOT EXISTS CONNECTIONS ( " +
"Id TEXT NOT NULL,\n" +
"Name TEXT NOT NULL,\n" +
"DbFile TEXT NOT NULL,\n" +
"Schema TEXT NOT NULL DEFAULT 'dbo',\n" +
"Key TEXT\n" +
")";
using (var cmd = new SqlDatabaseCommand(sql, con))
{
cmd.ExecuteNonQuery();
}
}
private void button1_Click(object sender, EventArgs e)
{
BackupData = new DataTable(Table.Name);
for (var i = 0; i < cbColumns.Items.Count; i++)
{
var col = this.Table.Columns.First(o => o == cbColumns.Items[i]);
col.IsPKey = cbColumns.GetItemChecked(i);
}
using (var con = new SqlDatabaseConnection(Table.ConnectionString))
{
con.Open();
using (var cmd = new SqlDatabaseCommand("SELECT * FROM " + Table.Name, con))
{
using (var da = new SqlDatabaseDataAdapter(cmd))
{
da.Fill(BackupData);
}
}
var transaction = con.BeginTransaction();
try
{
using (var cmd = new SqlDatabaseCommand("DROP TABLE " + Table.Name, con))
{
cmd.ExecuteNonQuery();
}
Utils.CreateTableFromDefinition(Table, con);
Utils.RestoreTableFromBackup(BackupData, con, transaction);
transaction.Commit();
this.DialogResult = DialogResult.OK;
}
catch (Exception ex)
{
transaction.Rollback();
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
con.Close();
}
}
}
public void InvokeCommand_Entity_To_Builder_Conversion_Throws_When_Entity_Could_Not_Be_Constructed()
{
// Arrange
var builderProviderMock = new Mock <IDatabaseCommandEntityProvider <TestEntity, TestEntityBuilder> >();
builderProviderMock.SetupGet(x => x.CreateBuilderDelegate)
.Returns(new Func <TestEntity, TestEntityBuilder>(entity => new TestEntityBuilder(entity)));
var command = new SqlDatabaseCommand("INSERT INTO ...", DatabaseCommandType.Text, DatabaseOperation.Insert);
var builderSut = new DatabaseCommandProcessor <TestEntity, TestEntityBuilder>(Connection, builderProviderMock.Object);
var entity = new TestEntity("A", "B", "C", true);
// Act & Assert
builderSut.Invoking(x => x.ExecuteCommand(command, entity))
.Should().Throw <InvalidOperationException>()
.WithMessage("Could not cast type [CrossCutting.Data.Sql.Tests.Repositories.TestEntityBuilder] to [CrossCutting.Data.Sql.Tests.Repositories.TestEntity]");
}
// private static readonly string EnsureTableQuery = @"SELECT * FROM {0}.{1}";
public static async Task EnsureTableAsync(SqlDatabaseConnection conn, ReplicationTable table)
{
// create table
var querySb = new StringBuilder($@"CREATE TABLE IF NOT EXISTS
{Utility.Utility.GetSafeName(table.SchemaName)}.{Utility.Utility.GetSafeName(table.TableName)}(");
var primaryKeySb = new StringBuilder("PRIMARY KEY (");
var hasPrimaryKey = false;
foreach (var column in table.Columns)
{
querySb.Append(
$"{Utility.Utility.GetSafeName(column.ColumnName)} {column.DataType}{(column.PrimaryKey ? " NOT NULL UNIQUE" : "")},");
if (column.PrimaryKey)
{
primaryKeySb.Append($"{Utility.Utility.GetSafeName(column.ColumnName)},");
hasPrimaryKey = true;
}
}
if (hasPrimaryKey)
{
primaryKeySb.Length--;
primaryKeySb.Append(")");
querySb.Append($"{primaryKeySb});");
}
else
{
querySb.Length--;
querySb.Append(");");
}
await conn.OpenAsync();
var query = querySb.ToString();
Logger.Debug($"Creating Table: {query}");
var cmd = new SqlDatabaseCommand
{
Connection = conn,
CommandText = query
};
await cmd.ExecuteNonQueryAsync();
await conn.CloseAsync();
}
public static void Insert(SqlDatabaseConnection connection, DbSettingItem item)
{
const string sql = "INSERT INTO CONNECTIONS(Id, Name, DbFile, Key, Schema)\n" +
"VALUES \n" +
"(@Id, @Name, @DbFile, @Key, @Schema)";
using (var cmd = new SqlDatabaseCommand(sql, connection))
{
cmd.Parameters.Add("@Id", item.Id);
cmd.Parameters.Add("@Name", item.Name);
cmd.Parameters.Add("@DbFile", item.DbFile);
cmd.Parameters.Add("@Key", item.Key);
cmd.Parameters.Add("@Schema", item.Schema);
cmd.ExecuteNonQuery();
}
}
public static void Update(SqlDatabaseConnection connection, DbSettingItem item)
{
const string sql = "UPDATE CONNECTIONS SET \n" +
"Name=@Name, DbFile=@DbFile, Key=@Key, Schema=@Schema)\n" +
"Where Id=@Id";
using (var cmd = new SqlDatabaseCommand(sql, connection))
{
cmd.Parameters.Add("@Id", item.Id);
cmd.Parameters.Add("@Name", item.Name);
cmd.Parameters.Add("@DbFile", item.DbFile);
cmd.Parameters.Add("@Key", item.Key);
cmd.Parameters.Add("@Schema", item.Schema);
cmd.ExecuteNonQuery();
}
}
/// <summary>
/// Returns the Podcast statistics
/// </summary>
/// <returns></returns>
public IEnumerable <PodcastForStatistic> GetStatistics()
{
List <PodcastForStatistic> statistics = new List <PodcastForStatistic>();
// Get all podcasts
using (SqlDatabaseCommand cmd = this.GetDatabaseCommand())
{
cmd.CommandText.AppendLine(" SELECT PodcastID, SummaryTitle, ISNULL(AudioAlreadyDownloaded, 0) AS Downloaded, 'mp3' AS FileType FROM Podcast ")
.AppendLine(" UNION ")
.AppendLine(" SELECT PodcastID, SummaryTitle, ISNULL(VideoAlreadyDownloaded, 0) AS Downloaded, 'mp4' AS FileType FROM Podcast ")
.AppendLine(" UNION ")
.AppendLine(" SELECT PodcastID, SummaryTitle, ISNULL(VideoAlreadyDownloaded, 0) AS Downloaded, 'youtube' AS FileType FROM Podcast ")
.AppendLine(" ORDER BY PodcastID, FileType ");
statistics = cmd.ExecuteTable <PodcastForStatistic>().ToList();
}
// Add statistics values
using (SqlDatabaseCommand cmd = this.GetDatabaseCommand())
{
cmd.CommandText.AppendLine(" SELECT PodcastID, FileType, COUNT(*) AS Downloaded ")
.AppendLine(" FROM PodcastStatistic ")
.AppendLine(" GROUP BY PodcastID, FileType ");
var data = cmd.ExecuteTable(new { PodcastID = 0, FileType = "", Downloaded = 0 });
foreach (var item in data)
{
statistics.First(i => i.PodcastID == item.PodcastID && i.FileType == item.FileType)
.Downloaded += item.Downloaded;
}
}
// Add youtube statistics
Dictionary <int, int?> youtubeStat = GetYoutubeStatistics();
foreach (var item in statistics)
{
if (item.FileType == "youtube")
{
item.Downloaded = youtubeStat.ContainsKey(item.PodcastID) ? youtubeStat[item.PodcastID].Value : 0;
}
}
return(statistics);
}
private void MultiRead_Click(object sender, EventArgs e)
{
if (string.IsNullOrWhiteSpace(ExampleDatabaseFile))
{
return;
}
//build connection string
cb.Clear(); //clear any existing settings.
cb.Uri = ExampleDatabaseFile;
cb.SchemaName = "db";
using (SqlDatabaseConnection cnn = new SqlDatabaseConnection(cb.ConnectionString))
{
cnn.Open();
Parallel.For(0, Environment.ProcessorCount, new ParallelOptions {
MaxDegreeOfParallelism = Environment.ProcessorCount
}, i => {
try
{
using (SqlDatabaseCommand command = new SqlDatabaseCommand())
{
command.Connection = cnn;
command.CommandText = "SELECT ProductId, ProductName FROM Products ORDER BY ProductId LIMIT 10 OFFSET " + (10 * i) + ";";
SqlDatabaseDataReader rd = command.ExecuteReader();
while (rd.Read())
{
Debug.Write(Thread.CurrentThread.ManagedThreadId + "\t");
for (int c = 0; c < rd.VisibleFieldCount; c++)
{
Debug.Write(rd.GetValue(c) + "\t");
}
Debug.WriteLine("");
}
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
}
});
}
}
private void ParallelRead_Click(object sender, EventArgs e)
{
if (string.IsNullOrWhiteSpace(ExampleDatabaseFile))
{
return;
}
//build connection
cb.Clear(); //clear any previous settings
cb.Uri = ExampleDatabaseFile;
cb.MultipleActiveResultSets = true;
cb.ExtendedResultSets = false;
cb.SchemaName = "db";
using (SqlDatabaseConnection cnn = new SqlDatabaseConnection(cb.ConnectionString))
{
cnn.Open();
Parallel.For(0, Environment.ProcessorCount, new ParallelOptions {
MaxDegreeOfParallelism = Environment.ProcessorCount
}, i => {
try
{
using (SqlDatabaseCommand command = new SqlDatabaseCommand())
{
command.Connection = cnn;
command.CommandText = "SELECT ProductId, ProductName FROM Products ORDER BY ProductId LIMIT 10 OFFSET " + (10 * i) + ";";
SQLDatabaseResultSet[] cmdrs = command.ExecuteReader(true);
if ((cmdrs != null) && (cmdrs.Length > 0))
{
foreach (SQLDatabaseResultSet rs in cmdrs)
{
Debug.WriteLine("RowCount {0}", rs.RowCount);
}
}
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
}
});
}
}
public void InvokeCommand_AfterReadDelegate_Does_Not_Throw_When_ExecuteReader_Read_Returns_True()
{
// Arrange
Connection.AddResultForDataReader(new[] { new MyEntity {
Property = "test"
} });
var command = new SqlDatabaseCommand("INSERT INTO ...", DatabaseCommandType.Text, DatabaseOperation.Insert);
ProviderMock.SetupGet(x => x.AfterReadDelegate).Returns(new Func <MyEntity, DatabaseOperation, IDataReader, MyEntity>((x, _, _) => x));
// Act
var actual = Sut.ExecuteCommand(command, new MyEntity {
Property = "test"
}).HandleResult("MyEntity entity was not added");
// Assert
actual.Property.Should().Be("test");
}
/// <summary>
/// <see cref="IDataService.GetPodcastID(string)"/>
/// </summary>
/// <param name="shortUrl"></param>
/// <returns></returns>
public int?GetPodcastID(string shortUrl)
{
Podcast podcast = _podcasts?.FirstOrDefault(i => i.PodcastKey == shortUrl);
if (podcast != null)
{
return(podcast.PodcastID);
}
else
{
using (SqlDatabaseCommand cmd = this.GetDatabaseCommand())
{
cmd.CommandText.AppendLine(" SELECT PodcastID FROM Podcast WHERE PodcastKey = @Key ");
cmd.Parameters.AddWithValue("@Key", shortUrl);
return(cmd.ExecuteScalar <int?>());
}
}
}
public void Can_Construct_NonGeneric()
{
// Act
var actual = new SqlDatabaseCommand("A", DatabaseCommandType.StoredProcedure, DatabaseOperation.Update, new { Parameter = "Value" });
// Assert
actual.CommandText.Should().Be("A");
actual.CommandType.Should().Be(DatabaseCommandType.StoredProcedure);
actual.Operation.Should().Be(DatabaseOperation.Update);
actual.CommandParameters.Should().NotBeNull();
var parameters = actual.CommandParameters.ToExpandoObject() as IDictionary <string, object>;
parameters.Should().NotBeNull();
if (parameters != null)
{
parameters["Parameter"].Should().Be("Value");
}
}
private void IndexAndVacuum_Click(object sender, EventArgs e)
{
using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("schemaname=db;uri=file://" + ExampleDatabaseFile))
{
cnn.Open();
//CREATE INDEX IndexName ON TableName (Columns...)
// Also see online documentation
// http://www.sqldatabase.net/docs/create-index.aspx
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
{
cmd.CommandText = "ReIndex ; "; // Rebuild all indexes on all tables.
cmd.ExecuteNonQuery();
}
// After large delete or dropping of large table Vacuum will rearrange space.
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
{
cmd.CommandText = "VACUUM ; "; // Rearrange database pages
cmd.ExecuteNonQuery();
}
// Integrity Check in case something else write to file or any other issues.
// If integrity check is not equals to SQLDATABASE_OK then it can be fixed by rebuilding indexes.
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
{
cmd.CommandText = "SYSCMD Integrity_Check ; ";
if (!cmd.ExecuteScalar().Equals("SQLDATABASE_OK"))
{
cmd.CommandText = "ReIndex ; VACUUM ;";
cmd.ExecuteNonQuery();
}
}
// Not required since dispose also closes the connection
if (cnn.State != ConnectionState.Closed)
{
cnn.Close();
}
}
}
private void CreateTable(string schemaName, string tableName, List <Column> columns)
{
// setup db table
var querySb = new StringBuilder($"CREATE TABLE IF NOT EXISTS [{schemaName}].[{tableName}] (");
var primaryKeySb = new StringBuilder("PRIMARY KEY (");
var hasPrimaryKey = false;
foreach (var column in columns)
{
querySb.Append(
$"[{column.ColumnName}] VARCHAR({int.MaxValue}){(column.IsKey ? " NOT NULL" : "")},");
if (column.IsKey)
{
primaryKeySb.Append($"[{column.ColumnName}],");
hasPrimaryKey = true;
}
}
if (hasPrimaryKey)
{
primaryKeySb.Length--;
primaryKeySb.Append(")");
querySb.Append($"{primaryKeySb});");
}
else
{
querySb.Length--;
querySb.Append(");");
}
var query = querySb.ToString();
Logger.Debug($"Create table query: {query}");
var cmd = new SqlDatabaseCommand
{
Connection = _conn,
CommandText = query
};
cmd.ExecuteNonQuery();
}
private void ToInMemoryDatabase_Click(object sender, EventArgs e)
{
//Connection to physical database file
using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("SchemaName=db;uri=file://" + ExampleDatabaseFile))
{
cnn.Open();
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
{
cmd.CommandText = "SELECT COUNT(*) FROM db.SYS_OBJECTS;"; // db.SYS_OBJECTS is SchemaName.ObjectName
Debug.WriteLine("Object Count in db: " + cmd.ExecuteScalar());
cmd.CommandText = "ATTACH Database '@memory' AS 'memdb1' ; "; //Attach new database schema with name memdb1
cmd.ExecuteNonQuery();
cmd.CommandText = "BACKUP Database 'db' AS 'memdb1' ;";
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT COUNT(*) FROM memdb1.SYS_OBJECTS;";
Debug.WriteLine("Object Count in memdb1: " + cmd.ExecuteScalar());
// To Save In memory database to file take backup to disk.
string dbfilepath = Path.Combine(Path.GetDirectoryName(Application.ExecutablePath), "ExampleBackup.db");
if (File.Exists(dbfilepath))
{
File.Delete(dbfilepath);
}
cmd.CommandText = "BACKUP Database 'memdb1' AS '" + dbfilepath + "' ;";
cmd.ExecuteNonQuery();
if (File.Exists(dbfilepath))
{
Debug.WriteLine(string.Format("Backup file created at {0}", dbfilepath));
}
else
{
Debug.WriteLine(cmd.GetLastError());
}
}
}
}
public int Delete(string CollectionName, string Key)
{
if (!TableExistsOrCreated)
{
OpenConnectionAndCreateTableIfNotExists();
}
if ((string.IsNullOrWhiteSpace(CollectionName)) || (string.IsNullOrWhiteSpace(Key)))
{
throw new Exception("CollectionName and Key are required.");
}
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
{
cmd.CommandText = "DELETE FROM KeyValueStore WHERE RowId = (SELECT RowId FROM KeyValueStore WHERE CollectionName = @CollectionName AND Key = @Key LIMIT 1);";
cmd.Parameters.AddWithValue("@CollectionName", CollectionName);
cmd.Parameters.AddWithValue("@Key", Key);
return(cmd.ExecuteNonQuery());
}
}
public static DbSettingItem Read(SqlDatabaseConnection connection, string id)
{
const string sql = "SELECT * FROM CONNECTIONS WHERE Id=@Id";
DataTable dt = new DataTable("Connection");
using (var cmd = new SqlDatabaseCommand(sql, connection))
{
cmd.Parameters.Add("@Id", id);
using (SqlDatabaseDataAdapter da = new SqlDatabaseDataAdapter(cmd))
{
da.Fill(dt);
if (dt.Rows.Count == 0)
{
return(null);
}
return(FromRow(dt.Rows[0]));
}
}
}
public static List <DbSettingItem> List(SqlDatabaseConnection connection)
{
const string sql = "SELECT * FROM CONNECTIONS ORDER BY NAME";
DataTable dt = new DataTable("Connections");
var result = new List <DbSettingItem>();
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(sql, connection))
{
using (SqlDatabaseDataAdapter da = new SqlDatabaseDataAdapter(cmd))
{
da.Fill(dt);
foreach (DataRow row in dt.Rows)
{
result.Add(FromRow(row));
}
}
}
return(result);
}
/// <summary>
/// Add a new trace of podcast view in PodcastStatistic Table's DB
/// </summary>
/// <param name="podcastID">ID of podcast to trace</param>
/// <param name="fileExtension">Extension of file read</param>
public void WriteStatisticItem(int podcastID, string fileExtension)
{
using (SqlDatabaseCommand cmd = this.GetDatabaseCommand())
{
cmd.CommandText.AppendLine(" IF EXISTS(SELECT * FROM Podcast WHERE PodcastID = @PodcastID) ")
.AppendLine(" INSERT INTO PodcastStatistic (PodcastID, FileType, UserAgent, UserIP, UserCountry) ")
.AppendLine(" VALUES (@PodcastID, @FileType, @UserAgent, @UserIP, @UserCountry) ");
cmd.Parameters.AddValues(new
{
PodcastID = podcastID,
FileType = fileExtension,
UserAgent = HttpContext.Current.Request.UserAgent,
UserIP = HttpContext.Current.Request.UserHostAddress,
UserCountry = string.Empty // To retrieve later
});
cmd.ExecuteNonQuery();
}
}
public void InitializeData(ISqlDataObject table)
{
try
{
var sql = "SELECT * FROM " + table.Name + " LIMIT " + pageSize + " OFFSET " + start;
var sqlCount = "SELECT COUNT(*) FROM " + table.Name;
this.Text = table.Name;
connection = new SqlDatabaseConnection(table.ConnectionString);
connection.Open();
this.table = new DataTable(table.Name);
command = new SqlDatabaseCommand(sql, connection);
adapter = new SqlDatabaseDataAdapter(command);
adapter.Fill(this.table);
total = Convert.ToInt32(new SqlDatabaseCommand(sqlCount, connection).ExecuteScalar());
sourceTable = table;
hasPrimaryKey = table.Columns.Any(o => o.IsPKey);
btnCreatePKey.Enabled = !hasPrimaryKey;
btnCreatePKey.ToolTipText = hasPrimaryKey ? "Table has existent primary key" : "Recreate Table with primary Key";
btnCreatePKey.Visible = !hasPrimaryKey;
dataGridView1.AllowUserToResizeRows = false;
dataGridView1.DataSource = this.table;
if (table.GetType() == typeof(SqlDataView))
{
dataGridView1.ReadOnly = true;
dataGridView1.AllowUserToAddRows = false;
dataGridView1.AllowUserToDeleteRows = false;
}
UpdateRecordsInfo();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error loading Data", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/// <summary>
/// Gets the statisctics about these youtube videos: a list of {PodcastID, NumberOfYoutubeViews}.
/// </summary>
/// <remarks>
/// 1. Connect you to https://console.developers.google.com
/// 2. Create a project and activate "YouTube Data API"
/// 3. Create a new API key in section Credentials, and use this key below
/// </remarks>
private Dictionary <int, int?> GetYoutubeStatistics()
{
//var youtubeKeys = null;
// Search all Youtube keys.
using (SqlDatabaseCommand cmd = this.GetDatabaseCommand())
{
cmd.CommandText.AppendLine(" SELECT PodcastID, VideoYoutubeKey, 0 AS Views FROM Podcast ");
var youtubeKeys = cmd.ExecuteTable(new { PodcastID = 0, VideoYoutubeKey = "", Views = 0 }).ToArray();
// Youtube statistics URL
string keys = String.Join(",", youtubeKeys.Select(i => i.VideoYoutubeKey).ToArray());
string googleapi = $"https://www.googleapis.com/youtube/v3/videos?part=statistics&id={keys}&key={this.Configuration.YoutubeStatisticsApiKey}";
Dictionary <int, int?> statistics = new Dictionary <int, int?>();
// Read web content
using (HttpClient client = new HttpClient())
{
string googleResult = client.GetStringAsync(googleapi).Result;
// Deserialize
var definition = new { items = new[] { new { id = "", statistics = new { viewCount = 0 } } } };
var data = JsonConvert.DeserializeAnonymousType(googleResult, definition);
if (data != null && data.items != null && data.items.Length > 0)
{
foreach (var item in data.items)
{
statistics.Add(youtubeKeys.First(i => i.VideoYoutubeKey == item.id).PodcastID,
item.statistics?.viewCount);
}
return(statistics);
}
else
{
return(null);
}
}
}
}
public int ExportTable(string FilePathAndName, bool AppendToFile = false)
{
int _row_count = 0;
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(SQLDatabaseConnection))
{
if (SQLDatabaseTransaction != null)
{
cmd.Transaction = SQLDatabaseTransaction;
}
cmd.CommandText = string.Format("SELECT * FROM [{0}].[{1}]", SchemaName, TableName);
using (CsvWriter = new CsvFileWriter(FilePathAndName, AppendToFile, Encoding.UTF8))
{
SqlDatabaseDataReader dataReader = cmd.ExecuteReader();
List <string> ColumnNames = new List <string>();
// Write header i.e. column names
for (int i = 0; i < dataReader.VisibleFieldCount; i++)
{
if (dataReader.GetFieldType(i) != Type.GetType("byte[]")) // BLOB will not be written
{
ColumnNames.Add(dataReader.GetName(i)); //maintain columns in the same order as the header line.
CsvWriter.AddField(dataReader.GetName(i));
}
}
CsvWriter.SaveAndCommitLine();
// Write data i.e. rows.
while (dataReader.Read())
{
foreach (string ColumnName in ColumnNames)
{
CsvWriter.AddField(dataReader.GetString(dataReader.GetOrdinal(ColumnName))); //dataReader.GetOrdinal(ColumnName) provides the position.
}
CsvWriter.SaveAndCommitLine();
_row_count++; //Increase row count to track number of rows written.
}
}
}
return(_row_count);
}
public static async Task DropTableAsync(SqlDatabaseConnection conn, ReplicationTable table)
{
await conn.OpenAsync();
// TODO: delete file from disk
var query = string.Format(DropTableQuery,
Utility.Utility.GetSafeName(table.SchemaName),
Utility.Utility.GetSafeName(table.TableName)
);
var cmd = new SqlDatabaseCommand
{
Connection = conn,
CommandText = query
};
cmd.ExecuteNonQuery();
await conn.CloseAsync();
}
private void dropIndexToolStripMenuItem_Click(object sender, EventArgs e)
{
var node = tvMain.SelectedNode;
if (node == null || node.Tag == null || node.Tag.GetType() != typeof(SqlDataIndex))
{
return;
}
var idx = node.Tag as SqlDataIndex;
if (idx == null)
{
return;
}
if (MessageBox.Show("Really drop Index '" + idx.Name + "'?", "Confirm", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question) == DialogResult.Yes)
{
string sql = "DROP INDEX " + idx.Name;
using (var con = new SqlDatabaseConnection(idx.ConnectionString))
{
try
{
con.Open();
using (var cmd = new SqlDatabaseCommand(sql, con))
{
cmd.ExecuteNonQuery();
tvMain.Nodes.Remove(node);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
con.Close();
}
}
}
}