![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » ElevateDB Technical Support » Technical Articles » ElevateDB 2.0 .NET Data Provider Tips and Tricks |
{ // Execute the insertdevicedata.sql script that is located in the // application folder with the proper parameter // // SetupCommand object was created earlier for the entire form SetupCommand.LoadFromFile(@"\Program Files\CFQuotesApp\insertdevicedata.sql"); SetupCommand.CommandType = CommandType.Text; SetupCommand.Prepare(); SetupCommand.Parameters["SalesPersonID"].Value = SalesPersonID; SetupCommand.ExecuteNonQuery(); } }
{ // Find the orders placed on that date EDBCommand DataCommand = new EDBCommand; DataCommand.ParamCheck = true; DataCommand.CommandText = "SELECT * FROM Orders WHERE OrderDate = :OrderDateParam"; DataCommand.Prepare(); DataCommand.Parameters["OrderDateParam"].Value = new DateTime(2008, 1, 1); DataCommand.Execute(); // Show the number of orders placed in a message dialog box MessageBox.Show("Number of orders placed is " + DataCommand.RowsAffected.ToString()); DataCommand.Dispose(); }
{ // Finally, be sure to set up the proper identity ranges for the tables // // SetupCommand object was created earlier for the entire form SetupCommand.CommandText = "UpdateIdentityRanges"; SetupCommand.CommandType = CommandType.StoredProcedure; SetupCommand.Prepare(); SetupCommand.Parameters["SalesPersonID"].Value = SalesPersonID; SetupCommand.ExecuteNonQuery(); }
CREATE PROCEDURE "UpdateIdentityRanges" (IN "SalesPersonID" INTEGER) BEGIN DECLARE SalesPersonCursor CURSOR FOR SalesPersonStmt; DECLARE CustomerNoStart INTEGER DEFAULT 0; DECLARE QuoteNoStart INTEGER DEFAULT 0; -- Select the correct salesperson PREPARE SalesPersonStmt FROM 'SELECT * FROM SalesPerson WHERE ID = ?'; OPEN SalesPersonCursor USING SalesPersonID; -- Verify that the salesperson was found, and then alter the tables to -- ensure the proper seed value for the identity columns IF ROWCOUNT(SalesPersonCursor) = 0 THEN RAISE ERROR CODE 10000 MESSAGE 'Invalid salesperson ID'; ELSE FETCH FROM SalesPersonCursor (CustomerNoStart) INTO CustomerNoStart; FETCH FROM SalesPersonCursor (QuoteNoStart) INTO QuoteNoStart; -- Need to make sure to close SalesPerson table completely -- because it will have both the Customer and Quotes tables -- open due to the foreign key constraints CLOSE SalesPersonCursor; UNPREPARE SalesPersonStmt; EXECUTE IMMEDIATE 'ALTER TABLE Customer ALTER COLUMN No RESTART WITH ' + CAST(CustomerNoStart AS VARCHAR); EXECUTE IMMEDIATE 'ALTER TABLE Quotes ALTER COLUMN QuoteNo RESTART WITH ' + CAST(QuoteNoStart AS VARCHAR); END IF; END
{ // Find the orders placed on that date EDBCommand DataCommand = new EDBCommand; DataCommand.ParamCheck = true; DataCommand.CommandText = "SELECT * FROM Orders WHERE OrderDate = :OrderDateParam"; DataCommand.RequestSensitive = true; DataCommand.Prepare(); DataCommand.Parameters["OrderDateParam"].Value = new DateTime(2008, 1, 1); DataCommand.Execute(); if (DataCommand.Sensitive) { MessageBox.Show("Result set is sensitive"); } else { MessageBox.Show("Result set is insensitive"); } DataCommand.Dispose(); }
private void button1_Click(object sender, EventArgs e) { // Set up the connection and command EDBConnection DataConnection = new EDBConnection("Type=Remote;Database=Test; " + "Address=127.0.0.1;Port=12010;" + "UID=Administrator;PWD=EDBDefault"); EDBCommand DataCommand = new EDBCommand(); DataCommand.Connection = DataConnection; DataCommand.CommandText = "SELECT * FROM customer INNER JOIN Orders " + "ON Customer.CustNo=Orders.CustNo " + "WHERE Customer.State='FL'"; DataConnection.Open(); // Add the OnProgress event handler DataCommand.OnProgress += QueryProgress; DataCommand.Execute(); DataCommand.Dispose(); DataConnection.Dispose(); } private void QueryProgress(object sender, EDBProgressEventArgs e) { progressBar1.Value = e.PercentDone; }
{ // Find the orders placed on that date EDBCommand DataCommand = new EDBCommand; DataCommand.ParamCheck = true; DataCommand.CommandText = "SELECT * FROM Orders WHERE OrderDate = :OrderDateParam"; DataCommand.RequestPlan = true; DataCommand.Prepare(); DataCommand.Parameters["OrderDateParam"].Value = new DateTime(2008, 1, 1); DataCommand.Execute(); MessageBox.Show(DataCommand.Plan); DataCommand.Dispose(); }
public EDBConnection QuotesConnection = null; public EDBCommand CustomerCommand = null; public EDBDataCursor CustomerCursor = null; public EDBCommand QuoteCommand = null; public EDBDataCursor QuoteCursor = null; public MainForm() { InitializeComponent(); // Create the connection with the proper connection string QuotesConnection = new EDBConnection(@"Type=Local;ConfigPath=\quotes;" + "Database=Configuration;" + "UID=Administrator;PWD=EDBDefault;" + @"TempTablesPath=\temp"); // Create the customer cursor CustomerCommand = new EDBCommand(); CustomerCommand.Connection = QuotesConnection; CustomerCommand.CommandType = CommandType.TableDirect; CustomerCommand.CommandText = "Customer"; // Create the customer quotes cursor QuoteCommand = new EDBCommand(); QuoteCommand.Connection = QuotesConnection; QuoteCommand.CommandType = CommandType.TableDirect; QuoteCommand.CommandText = "Quotes"; } ~MainForm() { // Close all of the cursors and dispose of all command and connection objects CloseCursors(); if (QuoteCommand != null) { QuoteCommand.Dispose(); QuoteCommand = null; } if (CustomerCommand != null) { CustomerCommand.Dispose(); CustomerCommand = null; } if (QuotesConnection != null) { QuotesConnection.Dispose(); QuotesConnection = null; } } private void OpenCursors() { // Open the cursors and assign the event handlers for updating the display CustomerCursor = CustomerCommand.ExecuteCursor(); CustomerCursor.OnMove += CustomerCursor_Move; CustomerCursor.OnStateChange += CustomerCursor_StateChange; QuoteCursor = QuoteCommand.ExecuteCursor(); QuoteCursor.OnMove += QuoteCursor_Move; QuoteCursor.OnStateChange += QuoteCursor_StateChange; CustomerCursor.ReadFirst(); } private void CloseCursors() { // Close and dispose of all of the cursor objects if (QuoteCursor != null) { QuoteCursor.Dispose(); QuoteCursor = null; } if (CustomerCursor != null) { CustomerCursor.Dispose(); CustomerCursor = null; } } private void LoadCustomer() { // Display the customer information CustomerNoTextBox.Text = CustomerCursor.GetString(0); CustomerNameTextBox.Text = CustomerCursor.GetString(1); Address1TextBox.Text = CustomerCursor.GetString(2); Address2TextBox.Text = CustomerCursor.GetString(3); CityTextBox.Text = CustomerCursor.GetString(4); StateTextBox.Text = CustomerCursor.GetString(5); ZipTextBox.Text = CustomerCursor.GetString(6); CountryTextBox.Text = CustomerCursor.GetString(7); } private void SaveCustomer() { // Save all customer information into the current row CustomerCursor.SetString(1, CustomerNameTextBox.Text); CustomerCursor.SetString(2, Address1TextBox.Text); CustomerCursor.SetString(3, Address2TextBox.Text); CustomerCursor.SetString(4, CityTextBox.Text); CustomerCursor.SetString(5, StateTextBox.Text); CustomerCursor.SetString(6, ZipTextBox.Text); CustomerCursor.SetString(7, CountryTextBox.Text); if (CustomerCursor.State == EDBDataCursorState.Inserting) { CustomerCursor.SetInt32(8, SalesPersonID); } } private void UpdateCustomerButtons() { // Update the navigation and editing buttons if (CustomerCursor.State == EDBDataCursorState.Browsing) { FirstCustomerButton.Visible = true; PreviousCustomerButton.Visible = true; NextCustomerButton.Visible = true; LastCustomerButton.Visible = true; InsertCustomerButton.Visible = true; DeleteCustomerButton.Visible = true; SaveCustomerButton.Visible = false; CancelCustomerButton.Visible = false; if (CustomerCursor.BOF) { FirstCustomerButton.Enabled = false; PreviousCustomerButton.Enabled = false; if (CustomerCursor.EOF) { NextCustomerButton.Enabled = false; LastCustomerButton.Enabled = false; DeleteCustomerButton.Enabled = false; } else { NextCustomerButton.Enabled = true; LastCustomerButton.Enabled = true; DeleteCustomerButton.Enabled = true; } } else if (CustomerCursor.EOF) { NextCustomerButton.Enabled = false; LastCustomerButton.Enabled = false; if (CustomerCursor.BOF) { FirstCustomerButton.Enabled = false; PreviousCustomerButton.Enabled = false; DeleteCustomerButton.Enabled = false; } else { FirstCustomerButton.Enabled = true; PreviousCustomerButton.Enabled = true; DeleteCustomerButton.Enabled = true; } } else { FirstCustomerButton.Enabled = true; PreviousCustomerButton.Enabled = true; NextCustomerButton.Enabled = true; LastCustomerButton.Enabled = true; DeleteCustomerButton.Enabled = true; } } else if (CustomerCursor.State == EDBDataCursorState.Inserting || CustomerCursor.State == EDBDataCursorState.Updating) { FirstCustomerButton.Visible = false; PreviousCustomerButton.Visible = false; NextCustomerButton.Visible = false; LastCustomerButton.Visible = false; InsertCustomerButton.Visible = false; DeleteCustomerButton.Visible = false; SaveCustomerButton.Visible = true; CancelCustomerButton.Visible = true; } } private void UpdateCustomerRowCount() { // Update the displayed row count if (CustomerCursor.State == EDBDataCursorState.Inserting) { CustomerRowCountLabel.Text = "Inserting"; } else if (CustomerCursor.State == EDBDataCursorState.Updating) { CustomerRowCountLabel.Text = "Updating"; } else { CustomerRowCountLabel.Text = "Browsing (" + Convert.ToString(CustomerCursor.RowCount) + ")"; } } private void FilterQuotes() { // Reset the filter on the quotes cursor QuoteCursor.Filter = "CustomerNo = " + CustomerCursor.GetString(0); QuoteCursor.Filtered = true; QuoteCursor.ReadFirst(); } private void CustomerCursor_Move(object sender) { LoadCustomer(); UpdateCustomerButtons(); UpdateCustomerRowCount(); FilterQuotes(); } private void CustomerCursor_StateChange(object sender) { LoadCustomer(); UpdateCustomerButtons(); UpdateCustomerRowCount(); FilterQuotes(); } private void FirstCustomerButton_Click(object sender, EventArgs e) { CustomerCursor.ReadFirst(); } private void PreviousCustomerButton_Click(object sender, EventArgs e) { CustomerCursor.ReadPrevious(); } private void NextCustomerButton_Click(object sender, EventArgs e) { CustomerCursor.Read(); } private void LastCustomerButton_Click(object sender, EventArgs e) { CustomerCursor.ReadLast(); } private void InsertCustomerButton_Click(object sender, EventArgs e) { CustomerCursor.Insert(); } private void DeleteCustomerButton_Click(object sender, EventArgs e) { if (MessageBox.Show("Delete this customer ?", "Confirm Deletion", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) == DialogResult.Yes) { try { CustomerCursor.Delete(); } catch (Exception Error) { MessageBox.Show(Error.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1); } } } private void CancelCustomerButton_Click(object sender, EventArgs e) { CustomerCursor.Cancel(); } private void SaveCustomerButton_Click(object sender, EventArgs e) { SaveCustomer(); CustomerCursor.Post(); } private void CustomerNameTextBox_KeyPress(object sender, KeyPressEventArgs e) { // Automatically put the cursor into an insert or update state when // any key is pressed in an edit box (assigned to all edit boxes) if (CustomerCursor.State == EDBDataCursorState.Browsing) { if (CustomerCursor.RowCount == 0) { CustomerCursor.Insert(); } else { CustomerCursor.Update(); } } }
This web page was last updated on Wednesday, January 13, 2021 at 12:15 AM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |