/// <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); } } }
private async Task<OleDbConnection> CreateConnectionAsync(CancellationToken cancellationToken = default(CancellationToken)) { var con = new OleDbConnection(ConnectionString); await con.OpenAsync(cancellationToken).ConfigureAwait(false); //TODO: Add in needed PRAGMA statements return con; }
private static async Task<DataSet> SetDataSet(string path) { var ds = new DataSet(); var connectString = ""; //connection strings for excel files from and rest code of this method based on //http://www.aspsnippets.com/Articles/Read-and-Import-Excel-File-into-DataSet-or-DataTable-using-C-and-VBNet-in-ASPNet.aspx if (Path.GetExtension(path).Equals(".xlsx")) connectString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1;\""; else if (Path.GetExtension(path).Equals(".xls")) connectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0 Xml;HDR=YES;IMEX=1;\""; using (var oleDbConnection = new OleDbConnection(connectString)) { await oleDbConnection.OpenAsync(); var dt = oleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); if (dt == null) return null; var excelSheets = new String[dt.Rows.Count]; var t = 0; foreach (DataRow row in dt.Rows) { excelSheets[t] = row["TABLE_NAME"].ToString(); t++; } var query = string.Format("Select * from [{0}]", excelSheets[0]); using (var dataAdapter = new OleDbDataAdapter(query, oleDbConnection)) { dataAdapter.Fill(ds); } oleDbConnection.Close(); } return ds; }
/// <summary> /// Gets a list of all the files stored in the database /// </summary> public static async Task<List<string>> GetAllFileNamessAsync() { string connectionString = defaultConnectionString; using (var connection = new OleDbConnection(connectionString)) { try { await connection.OpenAsync(); string query = "SELECT Name FROM Files;"; using (var command = new OleDbCommand(query, connection)) { var reader = await command.ExecuteReaderAsync(); var list = new List<string>(); while (await reader.ReadAsync()) list.Add(reader.GetString(0)); return list; } } catch (Exception ex) { TextEditor.WPFMessageBoxException(ex); return null; } } }
public async Task<IList<ServiceZipDetailsSet>> GetServiceZips(string company, string filename, string month, int year) { var serviceZips = new List<ServiceZipDetailsSet>(); var conn = new OleDbConnection(string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\"", filename)); await conn.OpenAsync(); var cmd = new OleDbCommand(); cmd.Connection = conn; cmd.CommandText = "SELECT * FROM [Лист1$]"; var reader = await cmd.ExecuteReaderAsync(); while (reader.Read()) { int q = 0; if (reader["Quantity"] != DBNull.Value) int.TryParse(reader["Quantity"].ToString(), out q); if (reader["BlankNumber"].ToString().Trim() == "tr") { //here } serviceZips.Add(new ServiceZipDetailsSet { WorkDate = Convert.ToDateTime(reader["Date"]), BlankNumber = reader["BlankNumber"].ToString().Trim(), ZipName = reader["Work"].ToString().Trim(), StoreNumber = Convert.ToInt16(reader["StoreNumber"]), ZipPrice = Convert.ToDecimal(reader["Price"]), Company = company, ServiceMonth = month, ServiceYear = year, ZipQuantity = q }); } reader.Close(); conn.Close(); return serviceZips; }
public async Task<bool> Connect(string connectionString, CancellationToken token) { _conn = new OleDbConnection(connectionString); try { await _conn.OpenAsync(token).ConfigureAwait(false); } catch (Exception ex) { return Error_(false, "Failed to connect to database.", L.f + ex.Details(false, false)); } //return Debug_(true, "Successfully connected to database.", $"logged in as : “{userName}”"); return Debug_(true, "Successfully connected to database.", ""); }
public static async Task<long> NrRows(string pathToExcel,string SheetName= "[Sheet1$]") { using (var m = new OleDbConnection()) { m.ConnectionString = ExcelHelpers.BuildExcelConnectionString(pathToExcel, false); await m.OpenAsync(); var query = @"Select count(*) From " + SheetName; using (var cmd = new OleDbCommand(query, m)) { var dr = await cmd.ExecuteScalarAsync(); return long.Parse(dr.ToString()); } } }
//static void getLegalitySource(int multiversid) //{ // WebRequest request = HttpWebRequest.Create("http://gatherer.wizards.com/Pages/Card/Printings.aspx?multiverseid=" + multiversid); // request.Method = "GET"; // using (StreamReader reader = new StreamReader(request.GetResponse().GetResponseStream())) // { // string source = reader.ReadToEnd(); // getLegality(multiversid, source); // } //} //static void getLegality(int multiversid, string source) //{ // string legacy = null; // List<string> formats = new List<string>(); // List<string> legalities = new List<string>(); // int tablestart = source.IndexOf("<table class=\"cardList\" cellspacing=\"0\" cellpadding=\"2\">") + 1; // legacy = source.Substring(tablestart, (source.Length - tablestart)); // tablestart = legacy.IndexOf("<table class=\"cardList\" cellspacing=\"0\" cellpadding=\"2\">"); // int tableend = legacy.IndexOf("</table>", tablestart); // legacy = legacy.Substring(tablestart, (tableend - tablestart)); // string[] legacysplit = legacy.Split(new string[] { "<tr class=\"cardItem evenItem\">", "<tr class=\"cardItem oddItem\">" }, StringSplitOptions.RemoveEmptyEntries); // for (int i = 1; i < legacysplit.Length; i++) // { // string[] split = legacysplit[i].Split(new string[] { "<td style=\"width:40%;\">", "<td style=\"text-align:center;\">", "<td>" }, StringSplitOptions.RemoveEmptyEntries); // int end = split[1].IndexOf("</td>"); // string format = split[1].Substring(0, end); // format = format.Replace("\n", string.Empty); // format = format.Replace("\r", string.Empty); // format = format.Replace("\t", string.Empty); // format = format.Trim(); // formats.Add(format); // end = split[2].IndexOf("</td>"); // string legality = split[2].Substring(0, end); // legality = legality.Replace("\n", string.Empty); // legality = legality.Replace("\r", string.Empty); // legality = legality.Replace("\t", string.Empty); // legality = legality.Trim(); // legalities.Add(legality); // } // saveLegality(multiversid, formats, legalities); //} /// <summary> /// Takes all the information for this one card and stores it as a new card in the database. /// </summary> /// <param name="Card"></param> /// <returns></returns> private static async Task saveCard(Classes.CardInfo Card) { if (Card.cardtext == null) Card.cardtext = ""; if (Card.convmanacost == null) Card.convmanacost = ""; if (Card.power == null) Card.power = ""; if (Card.rarity == null) Card.rarity = ""; if (Card.toughness == null) Card.toughness = ""; try { OleDbConnection DBcon = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + DBPath); await DBcon.OpenAsync(); //opens OLEBD connection to Database OleDbCommand cmd = new OleDbCommand(); cmd.CommandText = "INSERT INTO Cards([MultiverseID], [Name], [ConvManaCost], [Type], [CardText], [Power], [Toughness], [Expansion], [Rarity], [ImgURL]) VALUES (@MultiverseID, @Name, @ConvManaCost, @Type, @CardText, @Power, @Toughness, @Expansion, @Rarity, @ImgURL)"; //Adds a new card and all the information for it to the CardTable cmd.Parameters.Add("@MultiverseID", OleDbType.Integer).Value = Card.multiverseid; cmd.Parameters.Add("@Name", OleDbType.VarChar).Value = Card.name; cmd.Parameters.Add("@ConvManaCost", OleDbType.VarChar).Value = Card.convmanacost; cmd.Parameters.Add("@Type", OleDbType.VarChar).Value = Card.type; cmd.Parameters.Add("@CardText", OleDbType.VarChar).Value = Card.cardtext; cmd.Parameters.Add("@Power", OleDbType.VarChar).Value = Card.power; cmd.Parameters.Add("@Toughness", OleDbType.VarChar).Value = Card.toughness; cmd.Parameters.Add("@Expansion", OleDbType.VarChar).Value = Card.expansion; cmd.Parameters.Add("@Rarity", OleDbType.VarChar).Value = Card.rarity; cmd.Parameters.Add("@ImgURL", OleDbType.VarChar).Value = Card.imgurl; cmd.Connection = DBcon; cmd.ExecuteNonQuery(); DBcon.Close(); Console.WriteLine(Card.multiverseid + " (" + Card.name + ") was added to the database."); } catch (Exception) { Console.WriteLine(Card.name + " was skipped"); }; }
/// <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; } }
/// <summary> /// Receives file bytes from database by given file name /// </summary> /// <param name="name">Name of file to load</param> public static async Task<byte[]> LoadFileAsync(string name) { string connectionString = defaultConnectionString; using (var connection = new System.Data.OleDb.OleDbConnection(connectionString)) { try { await connection.OpenAsync(); string query = "SELECT FileData FROM Files WHERE name = @name;"; using (var command = new System.Data.OleDb.OleDbCommand(query, connection)) { command.Parameters.AddWithValue("@name", name); return (byte[])await command.ExecuteScalarAsync(); } } catch(Exception ex) { TextEditor.WPFMessageBoxException(ex); return null; } } }
async Task<object> ExecuteQuery(string connectionString, string commandString) { OleDbConnection connection = null; try { using (connection = new OleDbConnection(connectionString)) { await connection.OpenAsync(); using (var command = new OleDbCommand(commandString, connection)) { List<object> rows = new List<object>(); using (OleDbDataReader reader = command.ExecuteReader()) { IDataRecord record = (IDataRecord)reader; while (await reader.ReadAsync()) { var dataObject = new ExpandoObject() as IDictionary<string, Object>; var resultRecord = new object[record.FieldCount]; record.GetValues(resultRecord); for (int i = 0; i < record.FieldCount; i++) { Type type = record.GetFieldType(i); if (resultRecord[i] is System.DBNull) { resultRecord[i] = null; } else if (type == typeof(byte[]) || type == typeof(char[])) { resultRecord[i] = Convert.ToBase64String((byte[])resultRecord[i]); } else if (type == typeof(Guid) || type == typeof(DateTime)) { resultRecord[i] = resultRecord[i].ToString(); } else if (type == typeof(IDataReader)) { resultRecord[i] = "<IDataReader>"; } dataObject.Add(record.GetName(i), resultRecord[i]); } rows.Add(dataObject); } return rows; } } } } catch(Exception e) { throw new Exception("ExecuteQuery Error", e); } finally { connection.Close(); } }
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(); } }