// этот метод создает ограничения на столбцы таблице на основе полученного объекта DataReader
private static void CreateSchemaFromReader(DataTable table, SqlCeDataReader reader)
{
DataTable schemaTable = reader.GetSchemaTable(); // Метод Возвращает таблицу описывающую метаданные столбца объекта SqlCeDataReader.
foreach (DataRow schemaRow in schemaTable.Rows)
{
DataColumn column = new DataColumn((string)schemaRow["ColumnName"]); // создание столбца с именем столбца в источнике данных
column.AllowDBNull = (bool)schemaRow["AllowDbNull"]; // получение значения свойства AllowDBNull
column.DataType = (Type)schemaRow["DataType"]; // получение значения свойства DataType
column.Unique = (bool)schemaRow["IsUnique"]; // получение значения свойства Unique
column.ReadOnly = (bool)schemaRow["IsReadOnly"]; // получение значения свойства Readonly
column.AutoIncrement = (bool)schemaRow["IsIdentity"]; // получение значения свойства AutoIncrement
if (column.DataType == typeof(string)) // если поле типа string
{
column.MaxLength = (int)schemaRow["ColumnSize"]; // получить значение свойства MaxLength
}
if (column.AutoIncrement == true) // Если поле с автоинкрементом
{
column.AutoIncrementStep = -1; column.AutoIncrementSeed = 0;
} // задать свойства AutoIncrementStep и AutoIncrementSeed
table.Columns.Add(column); // добавить созданный столбец в коллекцию Columns таблицы
}
}
public SqlCeDataReaderHelper(SqlCeDataReader dataReader)
{
_dataReader = dataReader;
var schemaTable = dataReader.GetSchemaTable();
if (schemaTable != null)
{
var schemaRows = schemaTable.Rows;
var count = schemaRows.Count;
_dataFieldReaders = new IDataFieldReader[count];
for (var i = 0; i < count; i++)
{
var schemaRow = FoundationDbColumnFactory.Create(schemaRows[i]);
var sqlCeType = (SqlCeType)schemaRows[i][SchemaTableColumn.ProviderType];
var sqlDbType = sqlCeType.SqlDbType;
IDataFieldReader dataFieldReader;
switch (sqlDbType)
{
case SqlDbType.Decimal:
dataFieldReader = new SqlDecimalFieldReader(dataReader, i);
break;
default:
dataFieldReader = new DefaultDataFieldReader(dataReader, i);
break;
}
_dataFieldReaders[i] = dataFieldReader;
}
}
}
private void button1_Click(object sender, EventArgs e)
{
string connectionString;
SqlCeEngine engine = new SqlCeEngine("Data Source=dbsdf0.sdf");
SqlCeConnection cnn;
connectionString = @"Data Source=dbsdf0.sdf";
cnn = new SqlCeConnection(connectionString);
cnn.Open();
MessageBox.Show("Connection open!");
SqlCeCommand cmd = new SqlCeCommand(" SELECT idutilizator, nume, prenume, datanasterii, datadecesului, numedeutilizator, parolautilizator FROM tabelutilizatori", cnn);
SqlCeDataReader reader = cmd.ExecuteReader();
DataTable td = new DataTable("tabelutilizatori");
td = reader.GetSchemaTable();
MessageBox.Show(td.Columns[0].ToString());
//this.Text = reader.GetValue(0).ToString();
//rez = -1 error ?
//MessageBox.Show(this.Text = reader.GetValue(0).ToString());
MessageBox.Show(cmd.ExecuteNonQuery().ToString());
cnn.Close();
MessageBox.Show("Connection close!");
}
/// <summary>Выбрать в таблицу</summary>
/// <param name="command">Комманда</param>
/// <param name="formatDic">Словать форматирования данных</param>
/// <returns>Таблица данных</returns>
public static DataTable SelectToTable(this SqlCeCommand command, Dictionary <string, Enum> formatDic)
{
SqlCeDataReader reader = command.ExecuteReader();
DataTable schemaTable = reader.GetSchemaTable();
DataTable table = new DataTable();
int index = 0;
if (schemaTable != null)
{
foreach (DataRow row in schemaTable.Rows)
{
Type type = reader.GetFieldType(index++);
if (type == typeof(DateTime))
{
type = typeof(string);
}
DataColumn column = new DataColumn(row["ColumnName"].ToString(), type);
table.Columns.Add(column);
}
while (reader.Read())
{
DataRow row = table.NewRow();
for (int i = 0; i < reader.FieldCount; i++)
{
Type type = reader[i].GetType();
switch (type.FullName)
{
case BaseFormatName.DBNull:
row[i] = string.Empty;
break;
case BaseFormatName.DateTime:
row[i] = GetDateTimeInFormat(reader.GetDateTime(i), formatDic);
break;
default:
row[i] = reader[i];
break;
}
}
table.Rows.Add(row);
}
}
return(table);
}
private DataTable ReaderToDT(SqlCeDataReader dataReader)
{
DataTable dt = new DataTable();
foreach (DataRow dr in dataReader.GetSchemaTable().Rows)
{
dt.Columns.Add(dr["ColumnName"] as string, (Type)(dr["DataType"]));
}
while (dataReader.Read())
{
DataRow dr = dt.NewRow();
foreach (DataColumn dc in dt.Columns)
{
dr[dc] = dataReader[dc.ColumnName];
}
dt.Rows.Add(dr);
}
return(dt);
}
private static int GetFieldSize(SqlCeConnection conn, string fieldName, int maxWidth, string commandText)
{
using (SqlCeCommand cmdSize = new SqlCeCommand(commandText))
{
cmdSize.Connection = conn;
using (SqlCeDataReader rdr = cmdSize.ExecuteReader(System.Data.CommandBehavior.SchemaOnly | System.Data.CommandBehavior.KeyInfo))
{
System.Data.DataTable schemaTable = rdr.GetSchemaTable();
System.Data.DataView schemaView = new System.Data.DataView(schemaTable);
schemaView.RowFilter = string.Format(CultureInfo.InvariantCulture, "ColumnName = '{0}'", fieldName);
if (schemaView.Count > 0)
{
string colName = schemaView[0].Row["BaseColumnName"].ToString();
string tabName = schemaView[0].Row["BaseTableName"].ToString();
using (SqlCeCommand cmd = new SqlCeCommand(string.Format(CultureInfo.InvariantCulture, "SELECT CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = '{0}' AND TABLE_NAME = '{1}'", colName, tabName)))
{
cmd.Connection = conn;
Object val = cmd.ExecuteScalar();
if (val != null)
{
if ((int)val < maxWidth)
{
return((int)val);
}
else
{
return(maxWidth);
}
}
}
}
}
}
return(-1);
}
/// <summary>
/// Converts a SqlCeDataReader to a DataSet
/// <param name='reader'>
/// SqlDataReader to convert.</param>
/// <returns>
/// DataSet filled with the contents of the reader.</returns>
/// </summary>
public static DataSet ConvertDataReaderToDataSet(SqlCeDataReader reader, string tabela)
{
DataSet dataSet = new DataSet();
do
{
// Create new data table
DataTable schemaTable = reader.GetSchemaTable();
DataTable dataTable = new DataTable(tabela);
if (schemaTable != null)
{
// A query returning records was executed
for (int i = 0; i < schemaTable.Rows.Count; i++)
{
DataRow dataRow = schemaTable.Rows[i];
// Create a column name that is unique in the data table
string columnName = (string)dataRow["ColumnName"]; //+ "<C" + i + "/>";
// Add the column definition to the data table
DataColumn column = new DataColumn(columnName, (Type)dataRow["DataType"]);
dataTable.Columns.Add(column);
}
dataSet.Tables.Add(dataTable);
// Fill the data table we just created
while (reader.Read())
{
DataRow dataRow = dataTable.NewRow();
for (int i = 0; i < reader.FieldCount; i++)
dataRow[i] = reader.GetValue(i);
dataTable.Rows.Add(dataRow);
}
}
else
{
// No records were returned
DataColumn column = new DataColumn("RowsAffected");
dataTable.Columns.Add(column);
dataSet.Tables.Add(dataTable);
DataRow dataRow = dataTable.NewRow();
dataRow[0] = reader.RecordsAffected;
dataTable.Rows.Add(dataRow);
}
}
while (reader.NextResult());
return dataSet;
}
private DataTable ReaderToDT(SqlCeDataReader dataReader)
{
DataTable dt = new DataTable();
foreach (DataRow dr in dataReader.GetSchemaTable().Rows)
{
dt.Columns.Add(dr["ColumnName"] as string, (Type)(dr["DataType"]));
}
while (dataReader.Read())
{
DataRow dr = dt.NewRow();
foreach (DataColumn dc in dt.Columns)
{
dr[dc] = dataReader[dc.ColumnName];
}
dt.Rows.Add(dr);
}
return dt;
}
