static void AddImage() { // Following example code inserts and retrive files/images from database Console.WriteLine("Example: How to add images or files in database."); using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("SchemaName=db;Uri=@memory")) //We will strore in memory for the example { cnn.Open(); using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn)) { cmd.CommandText = "CREATE TABLE Images (Id Integer Primary Key, FileName Text, ActualImage BLOB);"; cmd.ExecuteNonQuery(); cmd.CommandText = "INSERT INTO Images VALUES(@Id, @FileName, @Image);"; cmd.Parameters.Add("@Id"); cmd.Parameters.Add("@FileName"); cmd.Parameters.Add("@Image"); //Read the file if (File.Exists("ImageFile.png")) { byte[] byteArray = File.ReadAllBytes("ImageFile.png"); // Assign values to parameters. cmd.Parameters["@Id"].Value = 1; cmd.Parameters["@FileName"].Value = "ImageFile.png"; cmd.Parameters["@Image"].Value = byteArray; cmd.ExecuteNonQuery(); // Execute insert query Console.WriteLine("Image / File example read.."); cmd.CommandText = "SELECT FileName , ActualImage FROM Images WHERE Id = 1 LIMIT 1;"; SqlDatabaseDataReader dr = cmd.ExecuteReader(); while (dr.HasRows) { while (dr.Read()) { // Create Random file name so we won't overwrite the original actual file. string NewFileName = Path.GetRandomFileName() + dr["FileName"].ToString(); byte[] FileBytes = (byte[])dr.GetValue(dr.GetOrdinal("ActualImage")); File.WriteAllBytes(NewFileName, FileBytes); if (File.Exists(NewFileName)) { Console.WriteLine("File {0} created successfully.", NewFileName); } else { Console.WriteLine("Unable to create file {0}.", NewFileName); } } } } else { Console.WriteLine("File ImageFile.png not found."); } } } }
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 long ExportTable(string filePathAndName, bool appendToFile = false) { SQLDatabaseConnection.Open(); long rowCount = 0; using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(SQLDatabaseConnection)) { if (SQLDatabaseTransaction != null) { cmd.Transaction = SQLDatabaseTransaction; } cmd.CommandText = $@"SELECT * FROM [{SchemaName}].[{TableName}]"; using (DelimitedWriter = new DelimitedFileWriter(filePathAndName, appendToFile, Encoding.UTF8)) { // set variables DelimitedWriter.Delimiter = Delimiter; DelimitedWriter.QuoteWrap = ReplicationFormData.QuoteWrap; DelimitedWriter.NullValue = ReplicationFormData.NullValue; // write custom header to file if not empty if (!string.IsNullOrWhiteSpace(ReplicationFormData.CustomHeader)) { DelimitedWriter.WriteLineToFile(ReplicationFormData.CustomHeader); } SqlDatabaseDataReader dataReader = cmd.ExecuteReader(); List <string> columnNames = new List <string>(); // Write header i.e. column names for (int i = 0; i < dataReader.VisibleFieldCount; i++) { var name = dataReader.GetName(i); if (dataReader.GetFieldType(i) != Type.GetType("byte[]") && name != Constants.ReplicationRecordId && name != Constants.ReplicationVersionIds && name != Constants.ReplicationVersionRecordId) // BLOB will not be written { columnNames.Add(name); //maintain columns in the same order as the header line. DelimitedWriter.AddField(name); } } DelimitedWriter.SaveAndCommitLine(); // Write data i.e. rows. while (dataReader.Read()) { foreach (string columnName in columnNames) { DelimitedWriter.AddField( dataReader.GetString( dataReader.GetOrdinal( columnName))); //dataReader.GetOrdinal(ColumnName) provides the position. } DelimitedWriter.SaveAndCommitLine(); rowCount++; //Increase row count to track number of rows written. } } } return(rowCount); }