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");
}
