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