private bool LoadData(SqlCeConnection connection)
{
try
{
using (SqlCeCommand command = connection.CreateCommand())
{
command.CommandText = "UnitOfMeasurement";
command.CommandType = CommandType.TableDirect;
using (SqlCeDataAdapter adapter = new SqlCeDataAdapter(command))
{
DataTable unitsTable = new DataTable("UnitOfMeasurement");
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adapter.FillSchema(unitsTable, SchemaType.Source);
adapter.Fill(unitsTable);
unitsGrid.DataSource = unitsTable;
}
}
}
catch (Exception ex)
{
string message = "An error occurred in fetching the data from the database." +
"\nThe error text is as follows:\n" + Global.getExceptionText(ex);
SystemSounds.Exclamation.Play();
Cursor.Current = Cursors.Default;
MessageBox.Show(message, "Error in Fetching Data", MessageBoxButtons.OK, MessageBoxIcon.Error);
ErrorLogger.LogError(ex);
return(false);
}
return(true);
}
private bool loadData(SqlCeConnection connection)
{
try
{
unitsTable = loadUnits(connection);
if (unitsTable.Rows.Count == 0)
{
string message = "No unit of measurement (UoM) has been defined so far. " +
"\nThe items can only be defined after 1 or more UoM has been defined.";
System.Media.SystemSounds.Exclamation.Play();
MessageBox.Show(message, "UoM Not Defined", MessageBoxButtons.OK,
MessageBoxIcon.Exclamation);
toCloseForm = true;
return(false);
}
using (SqlCeCommand command = connection.CreateCommand())
{
command.CommandText = "Items";
command.CommandType = CommandType.TableDirect;
using (SqlCeDataAdapter adapter = new SqlCeDataAdapter(command))
{
DataTable table = new DataTable("Items");
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adapter.FillSchema(table, SchemaType.Source);
adapter.Fill(table);
itemsGrid.DataSource = table;
table.ColumnChanged += new DataColumnChangeEventHandler(table_ColumnChanged);
}
}
}
catch (Exception ex)
{
string message = "An error occurred in fetching the data from the database." +
"\nThe error text is as follows:\n" + Global.getExceptionText(ex);
SystemSounds.Exclamation.Play();
MessageBox.Show(message, "Error in Fetching Data", MessageBoxButtons.OK, MessageBoxIcon.Error);
ErrorLogger.LogError(ex);
toCloseForm = true;
return(false);
}
return(true);
}
public DataSet GetSchemaDataSet(List <string> tables)
{
DataSet schemaSet = null;
try
{
schemaSet = new DataSet();
using (SqlCeDataAdapter adapter = new SqlCeDataAdapter())
{
using (SqlCeCommand cmd = new SqlCeCommand())
{
cmd.Connection = _cn;
foreach (var table in tables)
{
string strSql = "SELECT * FROM [" + table + "] WHERE 1 = 0";
using (SqlCeCommand cmdSet = new SqlCeCommand(strSql, _cn))
{
using (SqlCeDataAdapter adapter1 = new SqlCeDataAdapter(cmdSet))
{
adapter1.FillSchema(schemaSet, SchemaType.Source, table);
//Fill the table in the dataset
cmd.CommandText = strSql;
adapter.SelectCommand = cmd;
adapter.Fill(schemaSet, table);
}
}
}
}
}
}
catch
{
if (schemaSet != null)
{
schemaSet.Dispose();
}
throw;
}
return(schemaSet);
}
public override StiDataColumnsCollection GetColumnsFromData(StiData data, StiDataSource dataSource)
{
StiDataColumnsCollection dataColumns = new StiDataColumnsCollection();
StiSqlSource sqlSource = dataSource as StiSqlSource;
try
{
if (sqlSource.SqlCommand != null && sqlSource.SqlCommand.Length > 0)
{
if (data.Data is SqlCeConnection)
{
SqlCeConnection connection = data.Data as SqlCeConnection;
OpenConnection(connection, data, dataSource.Dictionary);
using (SqlCeDataAdapter dataAdapter = new SqlCeDataAdapter(sqlSource.SqlCommand, connection))
{
DataTable dataTable = new DataTable();
dataTable.TableName = sqlSource.Name;
dataAdapter.SelectCommand.Prepare();
dataAdapter.FillSchema(dataTable, SchemaType.Source);
foreach (DataColumn column in dataTable.Columns)
{
dataColumns.Add(new StiDataColumn(column.ColumnName, column.Caption, column.DataType));
}
dataTable.Dispose();
}
CloseConnection(data, connection);
}
}
}
catch (Exception e)
{
StiLogService.Write(this.GetType(), e);
}
return dataColumns;
}
private bool loadData(SqlCeConnection connection)
{
try
{
using (SqlCeCommand command = connection.CreateCommand())
{
command.CommandText = "Customers";
command.CommandType = CommandType.TableDirect;
using (SqlCeDataAdapter adapter = new SqlCeDataAdapter(command))
{
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
DataTable table = new DataTable(command.CommandText);
adapter.FillSchema(table, SchemaType.Source);
adapter.Fill(table);
//create the expression column and populate it
createFormattedBalanceColumn(table);
customersGrid.DataSource = table;
}
}
}
catch (Exception ex)
{
string errorMessage = "An error occurred in fetching the data from the database." +
"\nThe error text is as follows:\n" +
Global.getExceptionText(ex);
SystemSounds.Exclamation.Play();
MessageBox.Show(errorMessage, "Error in Fetching Data", MessageBoxButtons.OK,
MessageBoxIcon.Error);
ErrorLogger.LogError(ex);
return(false);
}
return(true);
}
public void WriteToSQL(string message, string tags, SqlCeConnection databaseConnection)
{
if ((_name.Length == 0) && (active == true))
{
throw (new ApplicationException("Can't write to log, no name set", null));
}
else
{
DataSet data = new DataSet();
SqlCeDataAdapter adapter = new SqlCeDataAdapter(String.Format(
"SELECT * FROM {0}", _name), databaseConnection);
SqlCeCommandBuilder commands = new SqlCeCommandBuilder(adapter);
adapter.FillSchema(data, SchemaType.Source, _name);
DataRow newRow = data.Tables[_name].NewRow();
if (message.Length > 255)
{
message = message.Substring(0, 255);
}
newRow["time"] = DateTime.UtcNow;
newRow["tags"] = tags;
newRow["text"] = message;
data.Tables[_name].Rows.Add(newRow);
adapter.Update(data, _name);
}
}
/// <summary>
/// Click Event handler for the Query and Bind button
/// </summary>
private void btnExecute_Click(object sender, EventArgs e)
{
//Clear the text in the filter textbox above the datagrid
tbCommand.Text = String.Empty;
// Disable the button till we are done quering and binding
btnExecute.Enabled = false;
try
{
// Dispose previous views bound to the currently active RS
if (null != view1)
{
((IDisposable)view1).Dispose();
}
if (null != view2)
{
((IDisposable)view2).Dispose();
}
// Dispose previous SqlCeCommand and previous SqlCeResultSet
if (null != this.command)
{
this.command.Dispose();
}
if (null != this.resultSet)
{
this.resultSet.Dispose();
}
//Creates a Command with the associated connection
this.command = this.connection.CreateCommand();
// Use the SqlCeResultSet if the "Use DataSet" menu item is not
// checked
if (false == this.menuItemUseDataSet.Checked)
{
// Queury the Orders table in the Northwind database
this.command.CommandText = "SELECT * FROM Orders";
ResultSetOptions options = ResultSetOptions.Scrollable | ResultSetOptions.Sensitive;
if (this.menuItemUpdatable.Checked)
{
options |= ResultSetOptions.Updatable;
}
this.resultSet = this.command.ExecuteResultSet(options);
this.dataGrid.DataSource = null;
// Bind the result set to the controls
this.BindData();
}
else
{
//Retrieve the columns we are interested in from the Orders table
//Note that we do not specify this in the SqlCeResultSet queury above
// because we demonstrate the use of the Ordinals property in the
// ResultSetView.
string query = @"SELECT [Customer ID], [Ship Name],[Ship City]
,[Ship Country] FROM Orders";
this.command.CommandText = query;
table = new DataTable("Orders");
table.Locale = System.Globalization.CultureInfo.InvariantCulture;
SqlCeDataAdapter adapter = new SqlCeDataAdapter(this.command);
adapter.FillSchema(table, SchemaType.Source);
adapter.Fill(table);
this.dataGrid.DataSource = null;
this.BindData();
}
btnExecute.Enabled = true;
}
catch (InvalidOperationException ex)
{
btnExecute.Enabled = true;
MessageBox.Show(String.Format(System.Globalization.CultureInfo.CurrentCulture,
"Exception while Performing Query/Bind: \n {0}", ex.ToString()));
}
catch (SqlCeException ex)
{
btnExecute.Enabled = true;
ShowErrors(ex);
}
}
/// <summary>
/// Заполнить таблицу на основе заданного запроса, создать адаптер данных и команды на изменение данных
/// </summary>
private static void FillTable(DataTable dataTable, string sql)
{
SqlCeDataAdapter adapter = new SqlCeDataAdapter(sql, AppData.Conn);
adapter.ContinueUpdateOnError = true;
adapter.FillSchema(dataTable, SchemaType.Source); // для определения свойств столбцов AllowDBNull, MaxLength
adapter.Fill(dataTable);
dataTable.ExtendedProperties.Add("DataAdapter", adapter);
dataTable.BeginLoadData(); // выключить контроль ограничений вводимых данных
SqlCeCommandBuilder builder = new SqlCeCommandBuilder(adapter);
builder.ConflictOption = ConflictOption.OverwriteChanges;
}
/// <summary>
/// Заполнить схему данных таблицы
/// </summary>
public static void FillTableSchema(DataTable dataTable)
{
try
{
if (dataTable != null)
{
string tableName = dataTable.TableName == "User" ? "[User]" : dataTable.TableName;
string sql = "select * from " + tableName;
SqlCeDataAdapter adapter = new SqlCeDataAdapter(sql, AppData.Conn);
adapter.ContinueUpdateOnError = true;
adapter.FillSchema(dataTable, SchemaType.Source);
dataTable.ExtendedProperties.Add("DataAdapter", adapter);
dataTable.BeginLoadData(); // выключить контроль ограничений вводимых данных
SqlCeCommandBuilder builder = new SqlCeCommandBuilder(adapter);
builder.ConflictOption = ConflictOption.OverwriteChanges;
}
}
catch (Exception ex)
{
throw new Exception(AppPhrases.FillSchemaError + ":\r\n" + ex.Message);
}
}
protected string Insert_Data_Field_returnID(string Table_Name, e2Data[] UpdateData)
{
string sqlCMD = "SELECT ";
for (int i = 0; i < UpdateData.Length; i++)
{
if (i > 0)
{
sqlCMD += ", ";
}
sqlCMD += UpdateData[i].FieldName;
}
sqlCMD += " FROM " + Table_Name;
string lid;
if (using_transaction)
{
using (TransactionScope ts = new TransactionScope())
{
SqlCeDataAdapter sqlDbAdp = new SqlCeDataAdapter(sqlCMD, MsSqlConn);
DataSet myDataSet = new DataSet();
SqlCeCommandBuilder myCB = new SqlCeCommandBuilder(sqlDbAdp);
sqlDbAdp.FillSchema(myDataSet, SchemaType.Source, Table_Name);
DataRow newRow = myDataSet.Tables[Table_Name].NewRow();
for (int i = 0; i < UpdateData.Length; i++)
{
newRow[UpdateData[i].FieldName] = DbTypeConvert(UpdateData[i].FieldValue, myDataSet.Tables[Table_Name].Columns[UpdateData[i].FieldName].DataType);
}
myDataSet.Tables[Table_Name].Rows.Add(newRow);
sqlDbAdp.Update(myDataSet, Table_Name);
// Get Last Insert ID
SqlCeCommand lastId;
lastId = new SqlCeCommand();
lastId.Connection = MsSqlConn;
lastId.CommandText = ("SELECT LAST_INSERT_ID()");
try
{
lid = lastId.ExecuteScalar().ToString();
}
catch
{
lid = null;
}
ts.Complete();
}
}
else
{
SqlCeDataAdapter sqlDbAdp = new SqlCeDataAdapter(sqlCMD, MsSqlConn);
DataSet myDataSet = new DataSet();
SqlCeCommandBuilder myCB = new SqlCeCommandBuilder(sqlDbAdp);
sqlDbAdp.FillSchema(myDataSet, SchemaType.Source, Table_Name);
DataRow newRow = myDataSet.Tables[Table_Name].NewRow();
for (int i = 0; i < UpdateData.Length; i++)
{
newRow[UpdateData[i].FieldName] = DbTypeConvert(UpdateData[i].FieldValue, myDataSet.Tables[Table_Name].Columns[UpdateData[i].FieldName].DataType);
}
myDataSet.Tables[Table_Name].Rows.Add(newRow);
sqlDbAdp.Update(myDataSet, Table_Name);
// Get Last Insert ID
SqlCeCommand lastId;
lastId = new SqlCeCommand();
lastId.Connection = MsSqlConn;
lastId.CommandText = ("SELECT LAST_INSERT_ID()");
try
{
lid = lastId.ExecuteScalar().ToString();
}
catch
{
lid = null;
}
}
return(lid);
}
protected void Insert_Data_Field(string Table_Name, e2Data[] UpdateData)
{
string sqlCMD = "SELECT ";
for (int i = 0; i < UpdateData.Length; i++)
{
if (i > 0)
{
sqlCMD += ", ";
}
sqlCMD += UpdateData[i].FieldName;
}
sqlCMD += " FROM " + Table_Name;
if (using_transaction)
{
using (TransactionScope ts = new TransactionScope())
{
SqlCeDataAdapter sqlDbAdp = new SqlCeDataAdapter(sqlCMD, MsSqlConn);
DataSet myDataSet = new DataSet();
SqlCeCommandBuilder myCB = new SqlCeCommandBuilder(sqlDbAdp);
sqlDbAdp.FillSchema(myDataSet, SchemaType.Source, Table_Name);
DataRow newRow = myDataSet.Tables[Table_Name].NewRow();
for (int i = 0; i < UpdateData.Length; i++)
{
newRow[UpdateData[i].FieldName] = DbTypeConvert(UpdateData[i].FieldValue, myDataSet.Tables[Table_Name].Columns[UpdateData[i].FieldName].DataType);
}
myDataSet.Tables[Table_Name].Rows.Add(newRow);
sqlDbAdp.Update(myDataSet, Table_Name);
ts.Complete();
}
}
else
{
SqlCeDataAdapter sqlDbAdp = new SqlCeDataAdapter(sqlCMD, MsSqlConn);
DataSet myDataSet = new DataSet();
SqlCeCommandBuilder myCB = new SqlCeCommandBuilder(sqlDbAdp);
sqlDbAdp.FillSchema(myDataSet, SchemaType.Source, Table_Name);
DataRow newRow = myDataSet.Tables[Table_Name].NewRow();
for (int i = 0; i < UpdateData.Length; i++)
{
newRow[UpdateData[i].FieldName] = DbTypeConvert(UpdateData[i].FieldValue, myDataSet.Tables[Table_Name].Columns[UpdateData[i].FieldName].DataType);
}
myDataSet.Tables[Table_Name].Rows.Add(newRow);
sqlDbAdp.Update(myDataSet, Table_Name);
}
}
