public string getDBData() { string text = ""; SqlCeConnection connection = new SqlCeConnection(connectionString); try { connection.Open(); SqlCeCommand cmd = connection.CreateCommand(); cmd.CommandText = "SELECT * FROM Accounts"; SqlCeDataReader data = cmd.ExecuteReader(); StringBuilder table = new StringBuilder(); table.AppendLine($" {data.GetName(0)} {data.GetName(1)} "); table.AppendLine("-----------------------------------------"); while (data.Read()) { table.AppendLine($" {data.GetString(0)} {data.GetString(1)} "); } text = table.ToString(); } finally { connection.Close(); } return(text); }
// private void LoadOneProduct( int intProductID) // { // // Append the desired ProductID to the SELECT statement. // string strSQL = strGetOneProduct + intProductID; // // // A connection, a command, and a reader. // SqlCeConnection connDB = new SqlCeConnection(strConn); // SqlCeCommand cmndDB = new SqlCeCommand(); // SqlCeDataReader drdrDB; // // // Open the connection. // connDB.Open(); // // // Submit the SQL statement and receive // // the SqlCeReader for the one-row // // results set. // drdrDB = cmndDB.ExecuteReader(); // // // Read the first (only) row. // // Display it. Close the reader. // if ( drdrDB.Read() ) // { // LoadControlsFromRow(drdrDB); // } // drdrDB.Close(); // // // Close the connection. // connDB.Close(); // } private void LoadControlsFromRow(SqlCeDataReader drdrDB) { // Transfer the colum titles and the field // contents of the current row from the // reader to the form//s controls. lblProductID.Text = drdrDB.GetName(0); textProductID.Text = drdrDB.GetValue(0).ToString(); lblProductName.Text = drdrDB.GetName(1); textProductName.Text = drdrDB.GetValue(1).ToString(); lblCategoryName.Text = drdrDB.GetName(2); textCategoryName.Text = drdrDB.GetValue(2).ToString(); }
public void ExportToXML(string tableName, string outputFile) { using (SqlCeConnection conn = new SqlCeConnection(_connecitonString)) { using (var sw = new StreamWriter(outputFile, false, Encoding.GetEncoding("utf-8"))) { sw.WriteLine("<?xml version=\"1.0\" encoding=\"" + Encoding.GetEncoding("utf-8").WebName + "\"?>"); sw.WriteLine("<DATA>"); string sql = string.Format("select * from {0}", tableName); SqlCeCommand cmd = new SqlCeCommand(sql, conn); cmd.CommandType = CommandType.Text; conn.Open(); SqlCeDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { sw.WriteLine("<item>"); for (int col = 0; col < reader.FieldCount; col++) { string columnName = reader.GetName(col); sw.WriteLine("<" + columnName + ">" + XMLizeString(reader[col].ToString()) + "</" + columnName + ">"); } sw.WriteLine("</item>"); } sw.WriteLine("</DATA>"); reader.Close(); } } }
private void WriteTable(string directoryPath, string tableName, Action <string> updateStatus) { int count = 0; int total = ExecuteScalar <int>($"SELECT COUNT(*) FROM {tableName}"); using (StreamWriter writter = new StreamWriter($"{directoryPath}/{tableName}.CSV")) { using (SqlCeCommand command = CreateCommand($"SELECT * FROM {tableName}")) { using (SqlCeDataReader dataReader = command.ExecuteReader()) { IEnumerable <int> fieldRange = Enumerable.Range(0, dataReader.FieldCount); CsvWriter.WriteRow(writter, fieldRange.Select(i => dataReader.GetName(i).ToLower()).ToArray()); while (dataReader.Read()) { updateStatus($"Exportando tabela \"{tableName}\" - linha {++count} de {total}."); CsvWriter.WriteRow( writter, fieldRange.Select(i => dataReader.GetValue(i)) .Select(i => { if (i is bool) { return(((bool)i) ? "1" : "0"); } else { return(i.ToString()); } }) .ToArray() ); } } }; } }
public int GetColumnIndex(string fieldName) { for (int i = 0; i < reader.FieldCount; i++) { if (reader.GetName(i) == fieldName) { return(i); } } return(-1); }
static void Main(string[] args) { var connectionString = $"Data Source = \"{SdfFilePath}\""; SqlCeConnection connection = null; SqlCeCommand command = null; SqlCeDataReader reader = null; try { connection = new SqlCeConnection(connectionString); connection.Open(); var query = "SELECT * FROM Petitioners"; command = new SqlCeCommand(query, connection); reader = command.ExecuteReader(); var json = new List <ExpandoObject>(); while (reader.Read()) { var item = new ExpandoObject(); Enumerable.Range(0, reader.FieldCount) .Skip(1) .Select(i => new { Field = reader.GetName(i), Value = reader[i] }) .ToList() .ForEach(data => ((IDictionary <string, Object>)item)[data.Field] = data.Value); json.Add(item); } using (var jsonWriter = new StreamWriter($"{JsonFilePath}", false, Encoding.Default)) { jsonWriter.WriteLine(JsonConvert.SerializeObject(json, Formatting.Indented)); }; } catch (Exception exception) { Console.WriteLine(exception); } finally { reader?.Close(); command?.Dispose(); connection?.Close(); } Console.WriteLine($"{Environment.NewLine}Press any key to exit."); Console.ReadKey(); }
static void Main(string[] args) { var connectionString = $"Data Source = \"{SdfFilePath}\""; SqlCeConnection connection = null; SqlCeCommand command = null; SqlCeDataReader reader = null; StreamWriter csvWriter = null; try { connection = new SqlCeConnection(connectionString); connection.Open(); var query = "SELECT * FROM Petitioners"; command = new SqlCeCommand(query, connection); reader = command.ExecuteReader(); csvWriter = new StreamWriter($"{CsvFilePath}", false, Encoding.Default); csvWriter.WriteLine(Enumerable.Range(0, reader.FieldCount) .Select(i => $"\"{reader.GetName(i)}\"") .Aggregate((current, next) => $"{current},{next}")); while (reader.Read()) { csvWriter.WriteLine(Enumerable.Range(0, reader.FieldCount) .Select(i => String.Format("\"{0}\"", reader.GetValue(i) .ToString() .Replace(',', '_') .Replace('\"', '_'))) .Aggregate((current, next) => $"{current},{next}")); } } catch (Exception exception) { Console.WriteLine(exception); } finally { csvWriter?.Close(); reader?.Close(); command?.Dispose(); connection?.Close(); } Console.WriteLine($"{Environment.NewLine}Press any key to exit."); Console.ReadKey(); }
static void Main(string[] args) { SqlCeConnection cn = new SqlCeConnection(@"Data Source = C:\Users\Виктор\Documents\Db-Test.db"); string sqlExpression = "SELECT * FROM Account WHERE CreateON > 2015"; cn.Open(); SqlCeCommand command = new SqlCeCommand(sqlExpression, cn); SqlCeDataReader reader = command.ExecuteReader(); // выводим названия столбцов Console.WriteLine("{0}\t{1}\t{2}", reader.GetName(0), reader.GetName(1), reader.GetName(2)); while (reader.Read()) // построчно считываем данные { object name = reader.GetValue(0); object create = reader.GetValue(1); object id = reader.GetValue(2); Console.WriteLine("{0} \t{1} \t{2}", name, create, id); } reader.Close(); Console.Read(); }
public string GetReportDataJson(int patientId) { var list = new List <Dictionary <string, object> >(); SqlCeConnection _Conn = new SqlCeConnection(conn); _Conn.Open(); SqlCeCommand cmd = new SqlCeCommand("Select * from ReportData Where PatientId=" + patientId, _Conn); SqlCeDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { var dict = new Dictionary <string, object>(); for (int i = 0; i < rdr.FieldCount; i++) { if (rdr.GetName(i) != "PatientId") { dict.Add(rdr.GetName(i), rdr.IsDBNull(i) ? null : rdr.GetValue(i)); } } list.Add(dict); } _Conn.Close(); return(JsonConvert.SerializeObject(list)); }
void CreateBuilder(SqlCeDataReader reader) { if (_reflectionBuilderProperties.TryGetValue(typeof(T), out _builderProperties)) { return; } _builderProperties = new PropertyInfo[reader.FieldCount]; for (Int32 i = 0; i < reader.FieldCount; i++) { _builderProperties[i] = typeof(T).GetProperty(reader.GetName(i)); } _reflectionBuilderProperties.Add(typeof(T), _builderProperties); }
public void ExportDataToCSV() { //export all talbes data connection.Open(); string [] tables = { "EyeTracker", "Simon", "Maze" }; for (int count = 0; count < 3; count++) { Console.WriteLine("" + tables[count]); string commandText = @"SELECT * FROM " + tables[count]; SqlCeCommand cmd = new SqlCeCommand(commandText, connection); SqlCeDataReader rdr = cmd.ExecuteReader(); TextWriter stream = new StreamWriter(new FileStream(tables[count] + "_Data.csv", FileMode.Create), Encoding.Default); for (int k = 0; k < rdr.FieldCount; k++) { stream.Write(rdr.GetName(k)); stream.Write(","); } stream.Write(System.Environment.NewLine); while (rdr.Read()) { for (int i = 0; i < rdr.FieldCount - 2; i++) { if (rdr[i] != null) { stream.Write(rdr[i].ToString()); stream.Write(","); } else { stream.Write(","); } } if (rdr[rdr.FieldCount - 1] != null) { stream.Write(rdr[0].ToString()); } stream.Write(System.Environment.NewLine); } stream.Close(); rdr.Close(); cmd.Dispose(); } connection.Close(); }
/// <summary> /// Creates the builder. /// </summary> /// <param name="reader">The reader.</param> /// <returns></returns> public static DataReaderReflectionBuilder <T> CreateBuilder(SqlCeDataReader reader) { DataReaderReflectionBuilder <T> builder; if (!(_dataReaderReflectionBuilders.TryGetValue(typeof(T), out builder))) { builder = new DataReaderReflectionBuilder <T>(); } builder._objProperties = new PropertyInfo[reader.FieldCount]; for (Int32 i = 0; i < reader.FieldCount; i++) { builder._objProperties[i] = typeof(T).GetProperty(reader.GetName(i)); } return(builder); }
public void ExportToJSON(string tableName, string outputFile) { using (SqlCeConnection conn = new SqlCeConnection(_connecitonString)) { conn.Open(); using (var sw = new StreamWriter(outputFile, false, Encoding.GetEncoding("utf-8"))) { string sql = string.Format("select * from {0}", tableName); SqlCeCommand cmd = conn.CreateCommand(); cmd.CommandText = sql; cmd.CommandType = CommandType.Text; SqlCeDataReader reader = cmd.ExecuteReader(); sw.WriteLine("\t[\n"); bool firstRow = true; while (reader.Read()) { if (!firstRow) { sw.WriteLine("\t,"); } if (firstRow) { firstRow = false; } sw.WriteLine("\t{\n"); for (int col = 0; col < reader.FieldCount; col++) { string columnName = reader.GetName(col); string delimiter = ","; if (col == reader.FieldCount - 1) { delimiter = ""; } sw.WriteLine("\t\t\"" + columnName + "\": \"" + GetJsonText(reader[col]) + "\"" + delimiter); } sw.WriteLine("\t}"); } reader.Close(); sw.WriteLine("\t]\n"); sw.Close(); } } }
public string Get(int id) { var dict = new Dictionary <string, object>(); SqlCeConnection _Conn = new SqlCeConnection(conn); _Conn.Open(); SqlCeCommand cmd = new SqlCeCommand("Select * from Patient Where p_id=" + id, _Conn); SqlCeDataReader rdr = cmd.ExecuteReader(); if (rdr.Read()) { for (int i = 0; i < rdr.FieldCount; i++) { dict.Add(rdr.GetName(i), rdr.IsDBNull(i) ? null : rdr.GetValue(i)); } } _Conn.Close(); return(JsonConvert.SerializeObject(dict)); }
public DataTable ExecuteQuery(String queryString) { DataTable result_L = new DataTable(); SqlCeCommand command_L = _sqlCeConnection.CreateCommand(); command_L.CommandText = queryString; command_L.CommandType = System.Data.CommandType.Text; SqlCeTransaction sqlCeTransaction = _sqlCeConnection.BeginTransaction(System.Data.IsolationLevel.Serializable); SqlCeDataReader dataReader = command_L.ExecuteReader(); for (int i = 0; i < dataReader.FieldCount; ++i) { result_L.Columns.Add(dataReader.GetName(i)); } while (dataReader.Read()) { Object[] rowData_L = new object[dataReader.FieldCount]; dataReader.GetValues(rowData_L); result_L.Rows.Add(rowData_L); } sqlCeTransaction.Commit(CommitMode.Immediate); return(result_L); }
public T QuerySingle <T>() { SqlCeCommand Command = new SqlCeCommand(Sql, (SqlCeConnection)_Connection); if (_Transaction != null) { Command.Transaction = (SqlCeTransaction)_Transaction; } Command.CommandType = commandType; foreach (Parameter P in Parameters) { Command.Parameters.Add(new SqlCeParameter(P.Name, (P.value == null ? DBNull.Value : P.value))); } List <string> properties = new List <string>(); foreach (System.Reflection.PropertyInfo P in typeof(T).GetProperties()) { properties.Add(P.Name); } //SureConnectionAlive(); SqlCeDataReader Dr = Command.ExecuteReader(); object obj = Activator.CreateInstance(typeof(T)); var columns = new List <string>(); for (int i = 0; i < Dr.FieldCount; i++) { columns.Add(Dr.GetName(i)); } var classProperties = typeof(T).GetProperties() .Where(Pro => Pro.PropertyType.UnderlyingSystemType.Namespace.ToLower() == typeof(T).Namespace.ToLower()) .Select(itm => new Goodzila.Struct.Property() { ClassObject = Activator.CreateInstance(itm.PropertyType.UnderlyingSystemType), PropertyName = itm.Name }); var X = typeof(T).GetProperties(); string s = typeof(T).Namespace.ToLower(); s = s; X = X; List <Goodzila.Struct.Property> Property = new List <Goodzila.Struct.Property>(); Property.AddRange(classProperties); //SureConnectionAlive(); if (Dr.Read()) { foreach (string column in columns) { object value = Dr[column]; if (value == DBNull.Value) { value = null; } if (column.IndexOf('.') != -1) { goto ClassProperty; } PropertyInfo propertyInfo = typeof(T).GetProperty(column); if (propertyInfo != null) { if (value != null) { propertyInfo.SetValue(obj, Convert.ChangeType(value, propertyInfo.PropertyType.UnderlyingSystemType), null); } } ClassProperty: if (column.IndexOf('.') == -1) { goto END; } string columnN = column.Split('.')[0]; string columnNs = columnN; string columnNs2 = columnN; foreach (var item in Property) { if (item.PropertyName.ToString().ToLower() == columnN.ToLower()) { object Cls = item.ClassObject; PropertyInfo property = Cls.GetType().GetProperty(column.Split('.')[1]); if (property != null) { if (value != null) { property.SetValue(Cls, Convert.ChangeType(value, property.PropertyType.UnderlyingSystemType), null); } } item.ClassObject = Cls; } } string Ali = "12"; Ali = Ali; END: continue; } } foreach (var item in Property) { PropertyInfo propertyInfo = typeof(T).GetProperty(item.PropertyName.ToString()); propertyInfo.SetValue(obj, item.ClassObject, null); } //while (Dr.Read()) //{ // foreach (string Prop in properties) // { // PropertyInfo numberPropertyInfo = typeof(T).GetProperty(Prop); // object value = Dr[Prop]; // if (value == DBNull.Value) // value = null; // numberPropertyInfo.SetValue(obj, value, null); // } // break; //} Dr.Close(); return((T)obj); }
public string GetSerializedData(ListQuerysDescarga QueryList, IEstadoCarga Estado) { StringBuilder sb = new StringBuilder(); SqlCeDataReader dr = null; SqlCeCommand cmd = new SqlCeCommand(); int I = 0; int J = 0; int nIndexRowCountReplace = 0; int nTableCount = 0; int nRowCount = 0; int nTotalRowsCount = 0; // Se adicionan las 2 primeras lineas con tags para poder remplazarlos al final sb.Append("TABLECOUNT: <<TABLECOUNT>>" + "\r\n"); sb.Append("TOTALROWCOUNT: <<TOTALROWCOUNT>>" + "\r\n"); nTableCount = 0; nTotalRowsCount = 0; try { this.OpenConnection(); for (I = 0; I < QueryList.Count; I++) { if (Estado.Cancelado) { break; } Estado.IniciarTabla(QueryList[I].TableName.ToUpper()); cmd.Connection = (SqlCeConnection)this.Connection; cmd.CommandText = QueryList[I].Query; if (cmd.CommandText.StartsWith("SELECT", StringComparison.CurrentCultureIgnoreCase)) { cmd.CommandType = CommandType.Text; } else { cmd.CommandType = CommandType.TableDirect; } dr = cmd.ExecuteReader(); if (dr.Read()) { nTableCount += 1; sb.Append("TABLE: " + QueryList[I].TableName + "\r\n"); nIndexRowCountReplace = sb.Length; sb.Append("ROWCOUNT: <<ROWCOUNT>> " + "\r\n"); // Se agrega una primer linea con los nombres de los campos for (J = 0; J < dr.FieldCount; J++) { sb.Append(dr.GetName(J) + "|"); } sb.Append("\r\n"); // Se agrega una segunda linea con los tipos de datos for (J = 0; J < dr.FieldCount; J++) { sb.Append(dr.GetFieldType(J).ToString() + "|"); } sb.Append("\r\n"); // Se agregan lineas con los datos de todos los registros nRowCount = 0; do { if (Estado.Cancelado) { break; } for (J = 0; J < dr.FieldCount; J++) { if (dr.IsDBNull(J)) { sb.Append("(null)|"); } else { if (dr.GetFieldType(J) == typeof(System.DateTime)) { sb.Append(dr.GetDateTime(J).ToString("yyyy/MM/dd HH:mm:ss") + "|"); } else { sb.Append(System.Convert.ToString(dr.GetValue(J)) + "|"); } } } sb.Append("\r\n"); nRowCount += 1; nTotalRowsCount += 1; } while (dr.Read()); dr.Close(); sb.Replace("<<ROWCOUNT>>", nRowCount.ToString(), nIndexRowCountReplace, 40); sb.Append("\r\n"); // Se agrega una linea adicional para delimitar los datos de la tabla } } sb.Replace("<<TABLECOUNT>>", nTableCount.ToString(), 0, 40); if (sb.Length < 60) { sb.Replace("<<TOTALROWCOUNT>>", nTotalRowsCount.ToString(), 0, sb.Length); } else { sb.Replace("<<TOTALROWCOUNT>>", nTotalRowsCount.ToString(), 0, 60); } } catch (Exception ex) { throw ex; } finally { if (this.Connection.State == ConnectionState.Open) { this.CloseConnection(); } } return(sb.ToString()); }
private static void CreateHeadings(SqlCeCommand cmd, SqlCeConnection conn, SqlCeDataReader rdr, int maxWidth, List <Column> headings) { for (int i = 0; i < rdr.FieldCount; i++) { // 18 different types // Calculate width as max of name or data type based width switch (rdr.GetDataTypeName(i)) { case "BigInt": int width = Math.Max(20, rdr.GetName(i).Length); headings.Add(new Column { Name = rdr.GetName(i), Width = width, PadLeft = true }); break; case "Binary": width = (Math.Max(GetFieldSize(conn, rdr.GetName(i), maxWidth, cmd.CommandText), rdr.GetName(i).Length) * 2) + 2; headings.Add(new Column { Name = rdr.GetName(i), Width = width, PadLeft = false }); break; case "Bit": width = Math.Max(5, rdr.GetName(i).Length); headings.Add(new Column { Name = rdr.GetName(i), Width = width, PadLeft = true }); break; case "DateTime": width = Math.Max(20, rdr.GetName(i).Length); headings.Add(new Column { Name = rdr.GetName(i), Width = width, PadLeft = false }); break; case "Float": width = Math.Max(24, rdr.GetName(i).Length); headings.Add(new Column { Name = rdr.GetName(i), Width = width, PadLeft = true }); break; case "Image": width = Math.Max(GetFieldSize(conn, rdr.GetName(i), maxWidth, cmd.CommandText), rdr.GetName(i).Length) + 2; headings.Add(new Column { Name = rdr.GetName(i), Width = width, PadLeft = false }); break; case "Int": width = Math.Max(11, rdr.GetName(i).Length); headings.Add(new Column { Name = rdr.GetName(i), Width = width, PadLeft = true }); break; case "Money": width = Math.Max(21, rdr.GetName(i).Length); headings.Add(new Column { Name = rdr.GetName(i), Width = width, PadLeft = true }); break; case "NChar": width = Math.Max(GetFieldSize(conn, rdr.GetName(i), maxWidth, cmd.CommandText), rdr.GetName(i).Length); headings.Add(new Column { Name = rdr.GetName(i), Width = width, PadLeft = false }); break; case "NText": width = Math.Max(GetFieldSize(conn, rdr.GetName(i), maxWidth, cmd.CommandText), rdr.GetName(i).Length); headings.Add(new Column { Name = rdr.GetName(i), Width = width, PadLeft = false }); break; case "Numeric": width = Math.Max(21, rdr.GetName(i).Length); headings.Add(new Column { Name = rdr.GetName(i), Width = width, PadLeft = true }); break; case "NVarChar": width = Math.Max(GetFieldSize(conn, rdr.GetName(i), maxWidth, cmd.CommandText), rdr.GetName(i).Length); headings.Add(new Column { Name = rdr.GetName(i), Width = width, PadLeft = false }); break; case "Real": width = Math.Max(14, rdr.GetName(i).Length); headings.Add(new Column { Name = rdr.GetName(i), Width = width, PadLeft = true }); break; case "RowVersion": width = Math.Max(8, rdr.GetName(i).Length) + 2; headings.Add(new Column { Name = rdr.GetName(i), Width = width, PadLeft = false }); break; case "SmallInt": width = Math.Max(6, rdr.GetName(i).Length); headings.Add(new Column { Name = rdr.GetName(i), Width = width, PadLeft = true }); break; case "TinyInt": width = Math.Max(3, rdr.GetName(i).Length); headings.Add(new Column { Name = rdr.GetName(i), Width = width, PadLeft = true }); break; case "UniqueIdentifier": width = Math.Max(36, rdr.GetName(i).Length); headings.Add(new Column { Name = rdr.GetName(i), Width = width, PadLeft = false }); break; case "VarBinary": width = Math.Max(GetFieldSize(conn, rdr.GetName(i), maxWidth, cmd.CommandText), rdr.GetName(i).Length) + 2; headings.Add(new Column { Name = rdr.GetName(i), Width = width, PadLeft = false }); break; default: break; } } }
private static string AddRecord(SqlCeDataReader reader, int i) { var Field_Name = reader.GetName(i); return(OpenXml(Field_Name) + reader.GetValue(i) + CloseXml(Field_Name) + "\n"); }