static void IdentityColumnValues()
{
using (SqlDatabaseConnection sqlcnn = new SqlDatabaseConnection("schemaName=db;uri=@memory"))
{
sqlcnn.Open();
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(sqlcnn))
{
cmd.CommandText = "CREATE TABLE IF NOT EXISTS TestTable(Id Integer Primary Key AutoIncrement, Name Text);";
cmd.ExecuteNonQuery();
// Id should be one (1) after first insert
cmd.CommandText = "INSERT INTO TestTable VALUES(null, 'Hello');";
cmd.ExecuteNonQuery();
//LastSequenceNumber requires table name
Console.WriteLine(string.Format("via cmd.LastSequenceNumber: {0}", cmd.LastSequenceNumber("TestTable")));
//LastInsertRowId is tracked on connection.
Console.WriteLine(string.Format("via sqlcnn.LastInsertRowId: {0}", sqlcnn.LastInsertRowId));
//last_insert_rowid() is tracked on connection and returns Int64
cmd.CommandText = "SELECT last_insert_rowid()";
Int64 LastID = (Int64)cmd.ExecuteScalar();
Console.WriteLine(string.Format("via SQL: {0}", LastID));
// Id should be two (2) after following insert.
cmd.CommandText = "INSERT INTO TestTable(Name) VALUES('World');";
cmd.ExecuteNonQuery();
//LastSequenceNumber requires table name
Console.WriteLine(string.Format("via cmd.LastSequenceNumber: {0}", cmd.LastSequenceNumber("TestTable")));
//LastInsertRowId is tracked on connection.
Console.WriteLine(string.Format("via sqlcnn.LastInsertRowId: {0}", sqlcnn.LastInsertRowId));
//last_insert_rowid is tracked on connection SQL Statement
cmd.CommandText = "SELECT last_insert_rowid()";
LastID = (Int64)cmd.ExecuteScalar();
Console.WriteLine(string.Format("via SQL: {0}", LastID));
//Attach another database file to same connection
cmd.CommandText = "ATTACH DATABASE '@memory' AS 'db1'";
cmd.ExecuteNonQuery();
// Create table on schema db1
cmd.CommandText = "CREATE TABLE IF NOT EXISTS db1.TestTable(Id Integer Primary Key AutoIncrement, Name Text);";
cmd.ExecuteNonQuery();
// Id should be one (1)
cmd.CommandText = "INSERT INTO db1.TestTable VALUES(null, 'Hello');";
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT last_insert_rowid()";
LastID = (Int64)cmd.ExecuteScalar();
Console.WriteLine(string.Format("via SQL from db1: {0}", LastID));
cmd.CommandText = "SELECT * FROM db1.TestTable WHERE Id = last_insert_rowid()";
SqlDatabaseDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Console.WriteLine(dr["Name"]);
}
}
}
}
public object Get(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 = "SELECT Value FROM KeyValueStore WHERE CollectionName = @CollectionName AND Key = @Key LIMIT 1;";
cmd.Parameters.AddWithValue("@CollectionName", CollectionName);
cmd.Parameters.AddWithValue("@Key", Key);
byte[] b = (byte[])cmd.ExecuteScalar();
if (b != null)
{
return(ObjectFromByteArray(b));
}
else
{
return(b);
}
}
}
private void SimpleTransaction_Click(object sender, EventArgs e)
{
using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("schemaname=db;uri=file://@memory;"))
{
cnn.Open();
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
{
cmd.CommandText = "Create Table If not exists temptable(Id Integer, TextValue Text) ; ";
cmd.ExecuteNonQuery();
SqlDatabaseTransaction trans = cnn.BeginTransaction();
cmd.Transaction = trans;
try
{
for (int i = 0; i < 1000; i++)
{
cmd.CommandText = "INSERT INTO temptable VALUES (" + i + ", 'AValue" + i + "');";
cmd.ExecuteNonQuery();
}
} catch (SqlDatabaseException sqlex)
{
trans.Rollback();
Debug.WriteLine(sqlex.Message);
}
finally
{
trans.Commit();
}
cmd.CommandText = "SELECT COUNT(*) FROM temptable;";
Debug.WriteLine(cmd.ExecuteScalar());
}
}
}
public int AddOrUpdate(string CollectionName, string Key, object Value)
{
int RowsAffected = -1;
if (!TableExistsOrCreated)
{
OpenConnectionAndCreateTableIfNotExists();
}
if ((string.IsNullOrWhiteSpace(CollectionName)) || (string.IsNullOrWhiteSpace(Key)))
{
throw new Exception("CollectionName and Key are required.");
}
byte[] b = new byte[0];
if (Value != null)
{
if (!(Value is byte[]))
{
b = ObjectToByteArray(Value);
}
else
{
b = (byte[])Value;
}
}
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
{
cmd.CommandText = "SELECT RowId FROM KeyValueStore WHERE CollectionName = @CollectionName AND Key = @Key LIMIT 1;";
cmd.Parameters.AddWithValue("@CollectionName", CollectionName);
cmd.Parameters.AddWithValue("@Key", Key);
cmd.Parameters.AddWithValue("@Value", b);
object ObjRowId = cmd.ExecuteScalar();
lock (LockObject)
{
if (ObjRowId != null)
{
int Id = int.Parse(ObjRowId.ToString());
cmd.CommandText = "UPDATE KeyValueStore SET Value = @Value WHERE RowId = (SELECT RowId FROM KeyValueStore WHERE CollectionName = @CollectionName AND Key = @Key LIMIT 1);";
RowsAffected = cmd.ExecuteNonQuery();
}
else
{
cmd.CommandText = "INSERT INTO KeyValueStore(CollectionName, Key, Value) VALUES(@CollectionName, @Key, @Value);";
RowsAffected = cmd.ExecuteNonQuery();
}
}
}
return(RowsAffected);
}
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());
}
}
}
}
static void SIUDOperations()
{
using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("schemaname=db;uri=file://@memory;")) // In Memory database.
{
cnn.Open();
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand())
{
cmd.Connection = cnn;
cmd.CommandText = "CREATE TABLE IF NOT EXISTS TestTable (Username TEXT PRIMARY KEY, FirstName TEXT, LastName TEXT);";
cmd.ExecuteNonQuery();
// INSERT
cmd.CommandText = "INSERT INTO TestTable VALUES ('jdoe', 'John' , 'DOE');";
cmd.ExecuteNonQuery();
// SELECT - Load DataTable
DataTable dt = new DataTable();
cmd.CommandText = "SELECT Username, FirstName, LastName FROM TestTable;";
using (SqlDatabaseDataAdapter da = new SqlDatabaseDataAdapter())
{
da.SelectCommand = cmd;
da.Fill(dt);
}
if (dt.Rows.Count > 0)
{
Console.WriteLine(string.Format("Total Rows {0}", dt.Rows.Count));
}
// UPDATE
cmd.CommandText = "UPDATE TestTable SET LastName = 'Doe' WHERE Username = '******'; ";
cmd.ExecuteNonQuery();
// DELETE
cmd.CommandText = "DELETE FROM TestTable WHERE Username = '******'; ";
cmd.ExecuteNonQuery();
// TRUNCATE - Library does not support truncate but it can be achived by recreating the table
cmd.CommandText = "SELECT sqltext FROM SYS_OBJECTS Where type = 'table' AND tablename = 'TestTable' LIMIT 1;";
object TableSQLText = cmd.ExecuteScalar();
if (!string.IsNullOrWhiteSpace(TableSQLText.ToString()))
{
cmd.CommandText = "DROP TABLE IF EXISTS TestTable;";
cmd.ExecuteNonQuery();
// Now recreate the table....
cmd.CommandText = TableSQLText.ToString();
cmd.ExecuteNonQuery();
}
}
}
}
static void SimpleTransaction()
{
using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("schemaname=db;uri=file://@memory;"))
{
cnn.Open();
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
{
cmd.CommandText = "Create Table If not exists temptable(Id Integer, TextValue Text) ; ";
cmd.ExecuteNonQuery();
SqlDatabaseTransaction trans = cnn.BeginTransaction();
cmd.Transaction = trans;
try
{
for (int i = 0; i < 1000; i++)
{
cmd.CommandText = "INSERT INTO temptable VALUES (" + i + ", 'A Value" + i + "');";
cmd.ExecuteNonQuery();
}
trans.Commit(); //Commit since all inserts are completed.
//Test if transaction is open
if (trans.IsOpen)
{
trans.Commit();
}
}
catch (SqlDatabaseException sqlex)
{
//Rollback as their was an error.
if (trans.IsOpen)
{
trans.Rollback();
}
Console.WriteLine(sqlex.Message);
}
cmd.CommandText = "SELECT COUNT(*) FROM temptable;";
Console.WriteLine("Table Record Count using COUNT(*) : {0}", cmd.ExecuteScalar());
// Pure SQL Way of starting and committing transaction.
cmd.CommandText = "BEGIN";
cmd.ExecuteNonQuery();
// Your SQL Statements and commands can reside here to run with-in a transaction.
// INSERT UPDATE and DELETE
cmd.CommandText = "COMMIT"; //ROLLBACK
cmd.ExecuteNonQuery();
}
}
}
/// <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?>());
}
}
}
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();
}
}
}
public static string GetCreateSql(ISqlDataObject templateTable)
{
var createSql = "DROP TABLE IF EXISTS " + templateTable.Name + ";\n" +
"CREATE TABLE " + templateTable.Name + "\n(";
if (templateTable.GetType() == typeof(SqlDataView))
{
createSql = "DROP VIEW IF EXISTS " + templateTable.Name + ";\r\n";
using (var con = new SqlDatabaseConnection(templateTable.ConnectionString))
{
con.Open();
try
{
var sql = string.Format("SELECT sqltext FROM SYS_OBJECTS WHERE\n" +
"type = 'view' \n" +
"AND name = '{0}'", templateTable.Name);
using (var cmd = new SqlDatabaseCommand(sql, con))
{
createSql += Convert.ToString(cmd.ExecuteScalar());
}
}
catch
{
throw;
}
finally
{
con.Close();
}
}
return(createSql);
}
var fields = new List <string>();
foreach (var col in templateTable.Columns.Where(o => !string.IsNullOrEmpty(o.Name)))
{
var s = "\n" + col.Name + " " + col.Type;
if (!col.Nullable || col.IsPKey)
{
s += " NOT NULL";
}
if (col.IsPKey)
{
s += " PRIMARY KEY";
}
if (col.IsPKey && col.AutoInc)
{
s += " AUTOINCREMENT ";
}
var sDefault = Convert.ToString(col.DefaultValue);
if (sDefault == "''")
{
sDefault = string.Empty;
}
if (!col.AutoInc && col.DefaultValue != null && col.DefaultValue != DBNull.Value && !string.IsNullOrEmpty(sDefault))
{
s += " DEFAULT ";
if (col.Type.ToUpper() == "TEXT" && !sDefault.StartsWith("'"))
{
sDefault = "'" + sDefault + "'";
}
s += sDefault;
}
fields.Add(s);
}
createSql += string.Join(",", fields);
createSql += "\n)";
return(createSql.Replace("\n", "\r\n"));
}
static void EncryptionDecryption()
{
Console.WriteLine("*************** Encrypted File Example *******************");
using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("SchemaName=db;uri=file://Encrypted.db;"))
{
cnn.Open();
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
{
//Entire Database File will be encrypted using AES 256
cmd.CommandText = "SYSCMD Key='SecretPassword';";
cmd.ExecuteNonQuery();
cmd.CommandText = "Create table if not exists Users(id integer primary key autoincrement, Username Text, Password Text); ";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO Users values(NULL, @username, @password);";
cmd.Parameters.AddWithValue("@username", "sysdba");
cmd.Parameters.AddWithValue("@password", "SecretPassword");
cmd.ExecuteNonQuery();
}
}
using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("SchemaName=db;uri=file://Encrypted.db;"))
{
cnn.Open();
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
{
//Entire Database File will be encrypted using AES 256
cmd.CommandText = "SYSCMD Key = 'SecretPassword'; "; //If incorrect password library will not respond.
cmd.ExecuteNonQuery();
// COLLATE BINARY performs case sensitive search for password
// see http://www.sqldatabase.net/docs/syscmd.aspx for available collation sequences.
cmd.CommandText = "SELECT Id FROM Users WHERE Username = @username AND Password = @password COLLATE BINARY;";
cmd.Parameters.AddWithValue("@username", "sysdba");
cmd.Parameters.AddWithValue("@password", "SecretPassword");
Console.WriteLine("User Found {0}", cmd.ExecuteScalar() == null ? "No" : "Yes");
}
}
Console.Write(string.Empty);
Console.WriteLine("*************** Encrypted Column Example *******************");
string RandomUserName = "******" + System.IO.Path.GetRandomFileName();
using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("SchemaName=db;uri=file://EncryptedColumn.db;"))
{
cnn.Open();
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
{
cmd.CommandText = "CREATE TABLE IF NOT EXISTS UsersCreditCards(Name Text Primary Key, CreditCardNumber Text); ";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO UsersCreditCards values(@Name, EncryptText(@CreditCardNumber , 'SecretPassword'));";
cmd.Parameters.AddWithValue("@Name", RandomUserName);
cmd.Parameters.AddWithValue("@CreditCardNumber", "1234-5678");
cmd.ExecuteNonQuery();
}
}
using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("SchemaName=db;uri=file://EncryptedColumn.db;"))
{
cnn.Open();
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
{
cmd.CommandText = "SELECT DecryptText(CreditCardNumber , 'SecretPassword') AS [CreditCardNumber] FROM UsersCreditCards WHERE Name = @Name LIMIT 1;";
cmd.Parameters.AddWithValue("@Name", RandomUserName);
Console.WriteLine("User {0} Credit Card Number is : {1}", RandomUserName, cmd.ExecuteScalar());
Console.WriteLine("*************** All Users *******************");
cmd.CommandText = "SELECT Name, DecryptText(CreditCardNumber , 'SecretPassword') AS CreditCardNumber FROM UsersCreditCards;";
SqlDatabaseDataReader dr = cmd.ExecuteReader();
for (int c = 0; c < dr.VisibleFieldCount; c++)
{
Console.Write(dr.GetName(c) + "\t");
}
Console.WriteLine(Environment.NewLine + "----------------------");
while (dr.Read())
{
for (int c = 0; c < dr.VisibleFieldCount; c++)
{
Console.Write(dr.GetValue(c) + "\t");
}
Console.WriteLine("");
}
;
}
}
if (File.Exists("Encrypted.db"))
{
File.Delete("Encrypted.db");
}
if (File.Exists("EncryptedColumn.db"))
{
File.Delete("EncryptedColumn.db");
}
}
static void MultipleCommandTransaction()
{
using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("schemaname=db;uri=file://@memory;"))
{
cnn.Open();
// Create a table
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
{
cmd.CommandText = "Create Table If not exists temptable(Id Integer, TextValue Text) ; ";
cmd.ExecuteNonQuery();
cmd.CommandText = "Create Table If not exists temptable2(Id Integer, TextValue Text) ; ";
cmd.ExecuteNonQuery();
}
// Start a transaction on this connection
SqlDatabaseTransaction trans = cnn.BeginTransaction();
try
{
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
{
cmd.Transaction = trans; // attach this Command object to transaction.
for (int i = 0; i < 10; i++)
{
cmd.CommandText = "INSERT INTO temptable VALUES (" + i + ", 'AValue" + i + "');";
cmd.ExecuteNonQuery();
}
}
// Other processes can run here.
// Transaction stays active even after command object is closed and can be attached to other objects.
//Create another command object and insert in temptable2 using same transaction.
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
{
cmd.Transaction = trans; // attach this Command object to transaction.
for (int i = 0; i < 10; i++)
{
cmd.CommandText = "INSERT INTO temptable2 VALUES (" + i + ", 'AValue" + i + "');";
cmd.ExecuteNonQuery();
}
}
trans.Commit();
}
catch (SqlDatabaseException sqlex)
{
trans.Rollback();
Console.WriteLine(sqlex.Message);
}
// Let's check the record count.
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
{
cmd.CommandText = "SELECT COUNT(*) FROM temptable;";
Console.WriteLine("Record Count temptable : {0}", cmd.ExecuteScalar());
cmd.CommandText = "SELECT COUNT(*) FROM temptable2;";
Console.WriteLine("Record Count temptable2 : {0}", cmd.ExecuteScalar());
}
}
}
private void ParallelInsert_Click(object sender, EventArgs e)
{
if (string.IsNullOrWhiteSpace(ExampleDatabaseFile))
{
return;
}
string[] Files = Directory.GetFiles(Path.Combine(Directory.GetParent(Path.GetDirectoryName(Application.ExecutablePath)).Parent.FullName, "csv"), "*.csv");
// Example use @memory but parallel insert can be against any database schema files
using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("schemaname=db;uri=file://@memory;"))
{
cnn.Open();
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
{
cmd.CommandText = "CREATE TABLE Transactions (StreetAddress Text, City Text, Zip Text, State Text, Beds Integer, Baths Integer";
cmd.CommandText += ", SQFT Text, PropertyType Text, SaleDate Text, Price Real, Latitude Text, Longitude Text);";
cmd.ExecuteNonQuery();
}
Parallel.For(0, Files.Length, i =>
{
string[] FileLines = File.ReadAllLines(Files[i]);
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand())
{
cmd.Connection = cnn;
cmd.CommandText = "INSERT INTO Transactions VALUES (@StreetAddress, @City, @Zip, @State, @Beds, @Baths, @SQFT, @PropertyType, @SaleDate, @Price, @Latitude, @Longitude); ";
//Add Query Parameters if they don't exists.
if (!cmd.Parameters.Contains("@StreetAddress"))
{
cmd.Parameters.Add(new SqlDatabaseParameter {
ParameterName = "@StreetAddress"
});
}
if (!cmd.Parameters.Contains("@City"))
{
cmd.Parameters.Add(new SqlDatabaseParameter {
ParameterName = "@City"
});
}
if (!cmd.Parameters.Contains("@Zip"))
{
cmd.Parameters.Add(new SqlDatabaseParameter {
ParameterName = "@Zip"
});
}
if (!cmd.Parameters.Contains("@State"))
{
cmd.Parameters.Add(new SqlDatabaseParameter {
ParameterName = "@State"
});
}
if (!cmd.Parameters.Contains("@Beds"))
{
cmd.Parameters.Add(new SqlDatabaseParameter {
ParameterName = "@Beds"
});
}
if (!cmd.Parameters.Contains("@Baths"))
{
cmd.Parameters.Add(new SqlDatabaseParameter {
ParameterName = "@Baths"
});
}
if (!cmd.Parameters.Contains("@SQFT"))
{
cmd.Parameters.Add(new SqlDatabaseParameter {
ParameterName = "@SQFT"
});
}
if (!cmd.Parameters.Contains("@PropertyType"))
{
cmd.Parameters.Add(new SqlDatabaseParameter {
ParameterName = "@PropertyType"
});
}
if (!cmd.Parameters.Contains("@SaleDate"))
{
cmd.Parameters.Add(new SqlDatabaseParameter {
ParameterName = "@SaleDate"
});
}
if (!cmd.Parameters.Contains("@Price"))
{
cmd.Parameters.Add(new SqlDatabaseParameter {
ParameterName = "@Price"
});
}
if (!cmd.Parameters.Contains("@Latitude"))
{
cmd.Parameters.Add(new SqlDatabaseParameter {
ParameterName = "@Latitude"
});
}
if (!cmd.Parameters.Contains("@Longitude"))
{
cmd.Parameters.Add(new SqlDatabaseParameter {
ParameterName = "@Longitude"
});
}
for (int l = 0; l < FileLines.Length; l++)
{
string[] Values = FileLines[l].Split(','); //split line based on comma
//// Method 1 starts here
cmd.Parameters["@StreetAddress"].Value = Values[0];
cmd.Parameters["@City"].Value = Values[1];
cmd.Parameters["@Zip"].Value = Values[2];
cmd.Parameters["@State"].Value = Values[3];
cmd.Parameters["@Beds"].Value = Values[4];
cmd.Parameters["@Baths"].Value = Values[5];
cmd.Parameters["@SQFT"].Value = Values[6];
cmd.Parameters["@PropertyType"].Value = Values[7];
cmd.Parameters["@SaleDate"].Value = Values[8];
cmd.Parameters["@Price"].Value = Values[9];
cmd.Parameters["@Latitude"].Value = Values[10];
cmd.Parameters["@Longitude"].Value = Values[11];
cmd.ExecuteNonQuery();
////Method 1 ends here
////Method 2 starts here
////Parameters can be acccessed via index to shorten the code
////Parameters must be created in correct order and their count must match with values from file.
////Comment the above code if you want to test following code.
//for (int v = 0; v < Values.Length; v++)
//{
// cmd.Parameters[v].Value = Values[v];
//}
//cmd.ExecuteNonQuery();
////Method 2 ends here
}
}
Debug.WriteLine("Finished Thread {0} with total lines {1} from file {2}", Thread.CurrentThread.ManagedThreadId, FileLines.Length, Files[i]);
});
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
{
cmd.CommandText = "SELECT Count(*) as [TotalTransactions] FROM Transactions;";
Debug.WriteLine("Total Transactions {0}", cmd.ExecuteScalar());
}
Debug.WriteLine("Finished All Files");
}
}