/// <summary>
/// Method to Insert Record in the Excel
/// S1. If the EmpNo =0, then the Operation is Skipped.
/// S2. If the Employee is already exist, then it is taken for Update
/// </summary>
/// <param name="Emp"></param>
public async Task<bool> InsertOrUpdateRowInExcelAsync(Employee Emp)
{
bool IsSave = false;
//S1
if (Emp.EmpNo != 0)
{
await Conn.OpenAsync();
Cmd = new OleDbCommand();
Cmd.Connection = Conn;
Cmd.Parameters.AddWithValue("@EmpNo", Emp.EmpNo);
Cmd.Parameters.AddWithValue("@EmpName", Emp.EmpName);
Cmd.Parameters.AddWithValue("@Salary", Emp.Salary);
Cmd.Parameters.AddWithValue("@DeptName", Emp.DeptName);
//S2
if (!CheckIfRecordExistAsync(Emp).Result)
{
Cmd.CommandText = "Insert into [Sheet1$] values (@EmpNo,@EmpName,@Salary,@DeptName)";
}
else
{
if (Emp.EmpName != String.Empty || Emp.DeptName != String.Empty)
{
Cmd.CommandText = "Update [Sheet1$] set EmpNo=@EmpNo,EmpName=@EmpName,Salary=@Salary,DeptName=@DeptName where EmpNo=@EmpNo";
}
}
int result = await Cmd.ExecuteNonQueryAsync();
if (result > 0)
{
IsSave = true;
}
Conn.Close();
}
return IsSave;
}
/// <summary>
/// Saves byte array to database
/// </summary>
/// <param name="name">File name</param>
/// <param name="file">File bytes</param>
public static async Task SaveFileAsync(string name, byte[] file)
{
string connectionString = defaultConnectionString;
using (var connection = new OleDbConnection(connectionString))
{
try
{
// Try to open connection to Db
await connection.OpenAsync();
string query;
// If there is a record with same name, use another query to overwrite its data
if (await FileExists(name, connection))
query = "UPDATE Files SET FileData = @data WHERE Name = @name;";
else
query = "INSERT INTO Files ([Name],[FileData]) VALUES (@name,@data);";
using (var command2 = new OleDbCommand(query, connection))
{
// Queries are parameterized as a measure to prevent SQL injection
command2.Parameters.AddWithValue("@name", name);
command2.Parameters.AddWithValue("@data", file);
// Nonquery method is good when there is nothing to return
await command2.ExecuteNonQueryAsync();
}
}
catch (Exception ex)
{
// Possible more complex exception handling
TextEditor.WPFMessageBoxException(ex);
}
}
}
/// <summary>
/// Creates and opens a SQL connection.
/// </summary>
/// <param name="cancellationToken">The cancellation token.</param>
/// <returns></returns>
/// <remarks>
/// The caller of this method is responsible for closing the connection.
/// </remarks>
private async Task<OleDbConnection> CreateConnectionAsync(CancellationToken cancellationToken = default(CancellationToken))
{
var con = new OleDbConnection(ConnectionString);
await con.OpenAsync(cancellationToken).ConfigureAwait(false);
if (m_ServerDefaultSettings == null)
{
var temp = new SqlServerEffectiveSettings();
await temp.ReloadAsync(con, null);
#if !Thread_Missing
Thread.MemoryBarrier();
#endif
m_ServerDefaultSettings = temp;
}
var sql = BuildConnectionSettingsOverride();
if (sql.Length > 0)
using (var cmd = new OleDbCommand(sql.ToString(), con))
await cmd.ExecuteNonQueryAsync();
return con;
}
private async void OleDBButton_Click(object sender, RoutedEventArgs e)
{
//此方式需要先安裝 http://www.microsoft.com/en-us/download/confirmation.aspx?id=23734
List<Student> students = GenerateRandomData(100);
try
{
SaveFileDialog saveDialog = new SaveFileDialog()
{
DefaultExt = ".xlsx",
FileName = "Sample",
Filter = "Excel |*.xlsx"
};
if (saveDialog.ShowDialog() == true)
{
string Connstring = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={saveDialog.FileName};Extended Properties='Excel 12.0 Xml;HDR=YES;'";
using (OleDbConnection Conn = new OleDbConnection(Connstring))
{
await Conn.OpenAsync();
//create sheet
string sql = "CREATE TABLE Test (ID int,Name VarChar,Age int,Gender VarChar,phone VarChar,Eng int,Math int)";
using (OleDbCommand cmd = new OleDbCommand(sql, Conn))
{
await cmd.ExecuteNonQueryAsync();
//insert data
cmd.CommandText = "Insert into Test values (@ID,@Name,@Age,@Gender,@phone,@Eng,@Math)";
for (int i = 0; i < students.Count; i++)
{
cmd.Parameters.Clear();
cmd.Parameters.Add("@ID", OleDbType.Integer).Value = students[i].ID;
cmd.Parameters.Add("@Name", OleDbType.VarChar).Value = students[i].Name;
cmd.Parameters.Add("@Age", OleDbType.Integer).Value = students[i].Age;
cmd.Parameters.Add("@Gender", OleDbType.VarChar).Value = students[i].Gender ? "男" : "女";
cmd.Parameters.Add("@phone", OleDbType.VarChar).Value = students[i].phone;
cmd.Parameters.Add("@Eng", OleDbType.Integer).Value = students[i].Eng;
cmd.Parameters.Add("@Math", OleDbType.Integer).Value = students[i].Math;
await cmd.ExecuteNonQueryAsync();
}
}
}
}
}
catch (Exception ex)
{
StatusText.Text = ex.Message;
}
}
async Task<object> ExecuteNonQuery(string connectionString, string commandString)
{
OleDbConnection connection = null;
try
{
using (connection = new OleDbConnection(connectionString))
{
await connection.OpenAsync();
using (var command = new OleDbCommand(commandString, connection))
{
return await command.ExecuteNonQueryAsync();
}
}
}
catch(Exception e)
{
throw new Exception("ExecuteNonQuery Error", e);
}
finally
{
connection.Close();
}
}
/// <summary>
/// Executes the specified operation asynchronously.
/// </summary>
/// <param name="executionToken"></param>
/// <param name="implementation"></param>
/// <param name="cancellationToken"></param>
/// <param name="state"></param>
/// <returns></returns>
protected override async Task<int?> ExecuteAsync(CommandExecutionToken<OleDbCommand, OleDbParameter> executionToken, CommandImplementationAsync<OleDbCommand> implementation, CancellationToken cancellationToken, object state)
{
if (executionToken == null)
throw new ArgumentNullException("executionToken", "executionToken is null.");
if (implementation == null)
throw new ArgumentNullException("implementation", "implementation is null.");
var currentToken = executionToken as AccessCommandExecutionToken;
if (currentToken == null)
throw new ArgumentNullException("executionToken", "only AccessCommandExecutionToken is supported.");
var startTime = DateTimeOffset.Now;
try
{
using (var con = await CreateConnectionAsync(cancellationToken).ConfigureAwait(false))
{
int? rows = null;
while (currentToken != null)
{
OnExecutionStarted(currentToken, startTime, state);
using (var cmd = new OleDbCommand())
{
cmd.Connection = con;
if (DefaultCommandTimeout.HasValue)
cmd.CommandTimeout = (int)DefaultCommandTimeout.Value.TotalSeconds;
cmd.CommandText = currentToken.CommandText;
cmd.CommandType = currentToken.CommandType;
foreach (var param in currentToken.Parameters)
cmd.Parameters.Add(param);
currentToken.ApplyCommandOverrides(cmd);
if (currentToken.ExecutionMode == AccessCommandExecutionMode.Materializer)
rows = await implementation(cmd);
else if (currentToken.ExecutionMode == AccessCommandExecutionMode.ExecuteScalarAndForward)
currentToken.ForwardResult(await cmd.ExecuteScalarAsync());
else
rows = await cmd.ExecuteNonQueryAsync();
executionToken.RaiseCommandExecuted(cmd, rows);
OnExecutionFinished(currentToken, startTime, DateTimeOffset.Now, rows, state);
}
currentToken = currentToken.NextCommand;
}
return rows;
}
}
catch (Exception ex)
{
if (cancellationToken.IsCancellationRequested) //convert Exception into a OperationCanceledException
{
var ex2 = new OperationCanceledException("Operation was canceled.", ex, cancellationToken);
OnExecutionError(executionToken, startTime, DateTimeOffset.Now, ex2, state);
throw ex2;
}
else
{
OnExecutionError(executionToken, startTime, DateTimeOffset.Now, ex, state);
throw;
}
}
}
public static void Main(string[] args)
{
var connectionString = @"Data Source=.;Initial Catalog=Northwind;Integrated Security=True;";
var connectionStringForExcel = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=file.xlsx;Extended Properties='Excel 12.0 Xml;HDR=YES';";
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlCommand command = new SqlCommand("SELECT COUNT(*) FROM Categories", connection);
int rows = (int)command.ExecuteScalar();
Console.WriteLine("All rows in table Categories are: {0}", rows);
}
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlCommand command = new SqlCommand("SELECT * FROM Categories", connection);
var rows = command.ExecuteReader();
while (rows.Read())
{
Console.WriteLine(rows["CategoryName"] + " " + rows["Description"]);
}
}
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlCommand command = new SqlCommand("SELECT p.ProductName as Product, c.CategoryName as Category FROM Categories c JOIN Products p ON c.CategoryID = p.CategoryID", connection);
var rows = command.ExecuteReader();
while (rows.Read())
{
Console.WriteLine("Product:" + rows["Product"] + "; Category:" + rows["Category"]);
}
}
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlCommand command = new SqlCommand(
@"INSERT INTO Products
(ProductName, SupplierID, CategoryID, Discontinued)
VALUES
(@productName,@supplierId,@categoryId,@discontinued)", connection);
command.Parameters.AddWithValue("@productName", "test");
command.Parameters.AddWithValue("@supplierId", 2);
command.Parameters.AddWithValue("@categoryId", 2);
command.Parameters.AddWithValue("@discontinued", 0);
command.ExecuteNonQuery();
}
using (SqlConnection dbConn = new SqlConnection(connectionString))
{
dbConn.Open();
SqlCommand cmd = new SqlCommand(
"SELECT CategoryID, Picture FROM Categories", dbConn);
SqlDataReader reader = cmd.ExecuteReader();
using (reader)
{
while (reader.Read())
{
var categoryName = (int)reader["CategoryID"];
var image = (byte[])reader["Picture"];
WriteBinaryFile(categoryName + ".jpg", image);
}
}
}
using (OleDbConnection dbCon = new OleDbConnection(connectionStringForExcel))
{
dbCon.Open();
OleDbCommand command = new OleDbCommand("SELECT * FROM [Sheet1$]", dbCon);
var reader = command.ExecuteReader();
while (reader.Read())
{
var name = reader["Name"];
var score = reader["Score"];
Console.WriteLine("Name: {0}, Score: {1}", name, score);
}
OleDbCommand com = new OleDbCommand("INSERT INTO [Sheet1$] VALUES(@Name, @Score)", dbCon);
com.Parameters.AddWithValue("@Name", "test");
com.Parameters.AddWithValue("@Score", 2);
com.ExecuteNonQueryAsync();
}
Find(connectionString);
}
