Windows VistaC++/CLIDBAWindows 2003Win32Visual Studio 2008.NET 3.0Windows XP.NET 2.0.NET 3.5C# 2.0C# 3.0HTMLVisual StudioSQL ServerWindowsC++.NETC#
C# / VB.NET / C++ CLI: Create, read and write MS Access (mdb, accdb), MySQL, SQL Server, SQL Server Compact and SQLite databases
Both MS Access formats (mdb and new - accdb), MSSQL 2008 R2, MSSQL CE, SQLite, MySQL
- Download MS_Access__mdb__in_CSharp.zip - 91.2 KB
- Download MS_Access__mdb__in_VB.NET.zip - 99.5 KB
- Download MS_Access__mdb__in_C___CLI.zip - 158.4 KB
- Download MS_Access__accdb__in_CSharp.zip - 114.2 KB
- Download MS_Access__accdb__in_VB.NET.zip - 106 KB
- Download MS_Access__accdb__in_C___CLI.zip - 164.4 KB
- Download MySQL_in_CSharp.zip - 409.7 KB
- Download MySQL_in_VB.NET.zip - 268.9 KB
- Download MySQL_in_C___CLI.zip - 330.3 KB
- Download SQL_Server_2008_R2_in_CSharp.zip - 60.9 KB
- Download SQL_Server_2008_R2_in_VB.NET.zip - 128.6 KB
- Download SQL_Server_2008_R2_in_C___CLI.zip - 136.8 KB
- Download SQL_Server_CE_in_CSharp.zip - 55.3 KB
- Download SQL_Server_CE_in_VB.NET.zip - 122.2 KB
- Download SQL_Server_CE_in_C___CLI.zip - 153.9 KB
- Download SQLite_in_CSharp.zip - 522.5 KB
- Download SQLite_in_VB.NET.zip - 559.9 KB
- Download SQLite_in_C___CLI.zip - 627 KB
Introduction
These 6 very simple examples demonstrate how to:
- connect mdb, accdb, MySQL, SQL Server R2 2008, SQL Server Compact, SQLite databases in C#, VB.NET and C++/CLI languages
- prevent errors if database or table isn't exists and recreate it
- read data from db to DataTable and show it in DataGridView
- save modified data to db by using Command Builder
- prevent id field editing
- prevent intrusive MessageBox when user trying to input letters into INT column cell
A bit later I will add WPF versions for C# and VB.NET sources and (may be) any etc.
Access (mdb)
Important:
- (C# and VB.NET only) set the project configuration to x86: provider used to connect databases is 32-bit application
- you can create table with space in it's/column's name by using [] characters (e.g [column name]), but you can't save changes in this table by using OleDbCommandBuilder
- Redistribution: mdb databases aren't requires any third-party libraries or MS Access to run. Provider is pre-installed on Win2000 and higher.
Code in C#:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO; // File.Exists()
using System.Data.OleDb; // OleDbConnection, OleDbDataAdapter, OleDbCommandBuilder
namespace MS_Access__mdb__in_CSharp
{
public partial class Form1 : Form
{
string DBPath;
OleDbConnection conn;
OleDbDataAdapter adapter;
DataTable dtMain;
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
DBPath = Application.StartupPath + "\\test.mdb";
// create DB via ADOX if not exists
if (!File.Exists(DBPath))
{
ADOX.Catalog cat = new ADOX.Catalog();
cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + DBPath);
cat = null;
}
// connect to DB
conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + DBPath);
conn.Open();
// create table "Table_1" if not exists
// DO NOT USE SPACES IN TABLE AND COLUMNS NAMES TO PREVENT TROUBLES WITH SAVING, USE _
// OLEDBCOMMANDBUILDER DON'T SUPPORT COLUMNS NAMES WITH SPACES
try
{
using (OleDbCommand cmd = new OleDbCommand("CREATE TABLE [Table_1] ([id] COUNTER PRIMARY KEY, [text_column] MEMO, [int_column] INT);", conn))
{
cmd.ExecuteNonQuery();
}
}
catch (Exception ex) {if (ex != null) ex = null; }
// get all tables from DB
using (DataTable dt = conn.GetSchema("Tables"))
{
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i].ItemArray[dt.Columns.IndexOf("TABLE_TYPE")].ToString() == "TABLE")
{
comboBoxTables.Items.Add(dt.Rows[i].ItemArray[dt.Columns.IndexOf("TABLE_NAME")].ToString());
}
}
}
}
private void buttonOK_Click(object sender, EventArgs e)
{
if (comboBoxTables.SelectedItem == null) return;
adapter = new OleDbDataAdapter("SELECT * FROM [" + comboBoxTables.SelectedItem.ToString() + "]", conn);
new OleDbCommandBuilder(adapter);
dtMain = new DataTable();
adapter.Fill(dtMain);
dtMain.Columns["id"].ReadOnly = true; // deprecate id field edit to prevent exceptions
dataGridView1.DataSource = dtMain;
}
private void Form1_FormClosed(object sender, FormClosedEventArgs e)
{
if (adapter == null) return;
adapter.Update(dtMain);
}
// show tooltip (not intrusive MessageBox) when user trying to input letters into INT column cell
private void dataGridView1_DataError(object sender, DataGridViewDataErrorEventArgs e)
{
if (dtMain.Columns[e.ColumnIndex].DataType == typeof(Int64) ||
dtMain.Columns[e.ColumnIndex].DataType == typeof(Int32) ||
dtMain.Columns[e.ColumnIndex].DataType == typeof(Int16))
{
Rectangle rectColumn;
rectColumn = dataGridView1.GetColumnDisplayRectangle(e.ColumnIndex, false);
Rectangle rectRow;
rectRow = dataGridView1.GetRowDisplayRectangle(e.RowIndex, false);
toolTip1.ToolTipTitle = "This field is for numbers only.";
toolTip1.Show(" ",
dataGridView1,
rectColumn.Left, rectRow.Top + rectRow.Height);
}
}
private void dataGridView1_MouseDown(object sender, MouseEventArgs e)
{
toolTip1.Hide(dataGridView1);
}
}
}
Code in VB.NET:
'=======================================================
'Service provided by Telerik (www.telerik.com)
'Conversion powered by NRefactory.
'Twitter: @telerik
'Facebook: facebook.com/telerik
'=======================================================
Imports System.IO
' File.Exists()
Imports System.Data.OleDb
' OleDbConnection, OleDbDataAdapter, OleDbCommandBuilder
Public Class Form1
Private DBPath As String
Private conn As OleDbConnection
Private adapter As OleDbDataAdapter
Private dtMain As DataTable
Public Sub New()
InitializeComponent()
End Sub
Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs)
DBPath = Application.StartupPath + "\test.mdb"
' create DB via ADOX if not exists
' NOTE: to use ADOX add reference to COM Microsoft ADO Ext. 2.6 for DDL and Security!
If Not File.Exists(DBPath) Then
Dim cat As New ADOX.Catalog()
cat.Create(Convert.ToString("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=") & DBPath)
cat = Nothing
End If
' connect to DB
conn = New OleDbConnection(Convert.ToString("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=") & DBPath)
conn.Open()
' create table "Table_1" if not exists
' DO NOT USE SPACES IN TABLE AND COLUMNS NAMES TO PREVENT TROUBLES WITH SAVING, USE _
' OLEDBCOMMANDBUILDER DON'T SUPPORT COLUMNS NAMES WITH SPACES
Try
Using cmd As New OleDbCommand("CREATE TABLE [Table_1] ([id] COUNTER PRIMARY KEY, [text_column] MEMO, [int_column] INT);", conn)
cmd.ExecuteNonQuery()
End Using
Catch ex As Exception
If ex IsNot Nothing Then
ex = Nothing
End If
End Try
' get all tables from DB
Using dt As DataTable = conn.GetSchema("Tables")
For i As Integer = 0 To dt.Rows.Count - 1
If dt.Rows(i).ItemArray(dt.Columns.IndexOf("TABLE_TYPE")).ToString() = "TABLE" Then
comboBoxTables.Items.Add(dt.Rows(i).ItemArray(dt.Columns.IndexOf("TABLE_NAME")).ToString())
End If
Next
End Using
End Sub
Private Sub buttonOK_Click(ByVal sender As Object, ByVal e As EventArgs)
If comboBoxTables.SelectedItem Is Nothing Then
Return
End If
adapter = New OleDbDataAdapter("SELECT * FROM [" + comboBoxTables.SelectedItem.ToString() + "]", conn)
Dim builder = New OleDbCommandBuilder(adapter)
dtMain = New DataTable()
adapter.Fill(dtMain)
dtMain.Columns("id").[ReadOnly] = True
' deprecate id field edit to prevent exceptions
dataGridView1.DataSource = dtMain
End Sub
Private Sub Form1_FormClosed(ByVal sender As Object, ByVal e As FormClosedEventArgs)
If adapter Is Nothing Then
Return
End If
adapter.Update(dtMain)
End Sub
' show tooltip (not intrusive MessageBox) when user trying to input letters into INT column cell
Private Sub dataGridView1_DataError(ByVal sender As Object, ByVal e As DataGridViewDataErrorEventArgs)
If dtMain.Columns(e.ColumnIndex).DataType Is GetType(Int64) Or _
dtMain.Columns(e.ColumnIndex).DataType Is GetType(Int32) Or _
dtMain.Columns(e.ColumnIndex).DataType Is GetType(Int16) Then
Dim rectColumn As Rectangle
rectColumn = DataGridView1.GetColumnDisplayRectangle(e.ColumnIndex, False)
Dim rectRow As Rectangle
rectRow = DataGridView1.GetRowDisplayRectangle(e.RowIndex, False)
ToolTip1.ToolTipTitle = "This field is for numbers only."
ToolTip1.Show(" ", DataGridView1, rectColumn.Left, rectRow.Top + rectRow.Height)
End If
End Sub
Private Sub dataGridView1_MouseDown(ByVal sender As Object, ByVal e As MouseEventArgs)
toolTip1.Hide(dataGridView1)
End Sub
'=======================================================
'Service provided by Telerik (www.telerik.com)
'Conversion powered by NRefactory.
'Twitter: @telerik
'Facebook: facebook.com/telerik
'=======================================================
End Class
'=======================================================
'Service provided by Telerik (www.telerik.com)
'Conversion powered by NRefactory.
'Twitter: @telerik
'Facebook: facebook.com/telerik
'=======================================================
Code in C++/CLI:
#pragma once
namespace MSAccessmdbinCCLI {
using namespace System;
using namespace System::ComponentModel;
using namespace System::Collections;
using namespace System::Windows::Forms;
using namespace System::Data;
using namespace System::Drawing;
using namespace System::IO; // File::Exists()
using namespace System::Data::OleDb; // OleDbConnection, OleDbDataAdapter, OleDbCommandBuilder
/// <summary>
/// Summary for Form1
///
/// WARNING: If you change the name of this class, you will need to change the
/// 'Resource File Name' property for the managed resource compiler tool
/// associated with all .resx files this class depends on. Otherwise,
/// the designers will not be able to interact properly with localized
/// resources associated with this form.
/// </summary>
public ref class Form1 : public System::Windows::Forms::Form
{
public:
Form1(void)
{
InitializeComponent();
//
//TODO: Add the constructor code here
//
}
protected:
/// <summary>
/// Clean up any resources being used.
/// </summary>
~Form1()
{
if (components)
{
delete components;
}
}
private: System::Windows::Forms::DataGridView^ dataGridView1;
private: System::Windows::Forms::Label^ label1;
private: System::Windows::Forms::ComboBox^ comboBoxTables;
private: System::Windows::Forms::Button^ buttonOK;
private: System::Windows::Forms::ToolTip^ toolTip1;
private: System::ComponentModel::IContainer^ components;
protected:
private:
/// <summary>
/// Required designer variable.
/// </summary>
#pragma region Windows Form Designer generated code
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
void InitializeComponent(void)
{
this->components = (gcnew System::ComponentModel::Container());
this->dataGridView1 = (gcnew System::Windows::Forms::DataGridView());
this->label1 = (gcnew System::Windows::Forms::Label());
this->comboBoxTables = (gcnew System::Windows::Forms::ComboBox());
this->buttonOK = (gcnew System::Windows::Forms::Button());
this->toolTip1 = (gcnew System::Windows::Forms::ToolTip(this->components));
(cli::safe_cast<System::ComponentModel::ISupportInitialize^ >(this->dataGridView1))->BeginInit();
this->SuspendLayout();
//
// dataGridView1
//
this->dataGridView1->Anchor = static_cast<System::Windows::Forms::AnchorStyles>((((System::Windows::Forms::AnchorStyles::Top | System::Windows::Forms::AnchorStyles::Bottom)
| System::Windows::Forms::AnchorStyles::Left)
| System::Windows::Forms::AnchorStyles::Right));
this->dataGridView1->ColumnHeadersHeightSizeMode = System::Windows::Forms::DataGridViewColumnHeadersHeightSizeMode::AutoSize;
this->dataGridView1->Location = System::Drawing::Point(12, 44);
this->dataGridView1->Name = L"dataGridView1";
this->dataGridView1->ShowCellToolTips = false;
this->dataGridView1->Size = System::Drawing::Size(493, 266);
this->dataGridView1->TabIndex = 0;
this->dataGridView1->MouseDown += gcnew System::Windows::Forms::MouseEventHandler(this, &Form1::dataGridView1_MouseDown);
this->dataGridView1->DataError += gcnew System::Windows::Forms::DataGridViewDataErrorEventHandler(this, &Form1::dataGridView1_DataError);
//
// label1
//
this->label1->Location = System::Drawing::Point(12, 9);
this->label1->Name = L"label1";
this->label1->Size = System::Drawing::Size(65, 32);
this->label1->TabIndex = 1;
this->label1->Text = L"Select table to show:";
//
// comboBoxTables
//
this->comboBoxTables->DropDownStyle = System::Windows::Forms::ComboBoxStyle::DropDownList;
this->comboBoxTables->FormattingEnabled = true;
this->comboBoxTables->Location = System::Drawing::Point(83, 12);
this->comboBoxTables->Name = L"comboBoxTables";
this->comboBoxTables->Size = System::Drawing::Size(165, 21);
this->comboBoxTables->TabIndex = 2;
//
// buttonOK
//
this->buttonOK->Location = System::Drawing::Point(254, 12);
this->buttonOK->Name = L"buttonOK";
this->buttonOK->Size = System::Drawing::Size(56, 21);
this->buttonOK->TabIndex = 3;
this->buttonOK->Text = L"OK";
this->buttonOK->UseVisualStyleBackColor = true;
this->buttonOK->Click += gcnew System::EventHandler(this, &Form1::buttonOK_Click);
//
// toolTip1
//
this->toolTip1->AutoPopDelay = 5000;
this->toolTip1->InitialDelay = 0;
this->toolTip1->IsBalloon = true;
this->toolTip1->ReshowDelay = 0;
this->toolTip1->ToolTipIcon = System::Windows::Forms::ToolTipIcon::Warning;
this->toolTip1->UseAnimation = false;
this->toolTip1->UseFading = false;
//
// Form1
//
this->AutoScaleDimensions = System::Drawing::SizeF(6, 13);
this->AutoScaleMode = System::Windows::Forms::AutoScaleMode::Font;
this->ClientSize = System::Drawing::Size(517, 322);
this->Controls->Add(this->buttonOK);
this->Controls->Add(this->comboBoxTables);
this->Controls->Add(this->label1);
this->Controls->Add(this->dataGridView1);
this->Name = L"Form1";
this->Text = L"Form1";
this->Load += gcnew System::EventHandler(this, &Form1::Form1_Load);
this->FormClosed += gcnew System::Windows::Forms::FormClosedEventHandler(this, &Form1::Form1_FormClosed);
(cli::safe_cast<System::ComponentModel::ISupportInitialize^ >(this->dataGridView1))->EndInit();
this->ResumeLayout(false);
}
#pragma endregion
String ^DBPath;
OleDbConnection ^conn;
OleDbDataAdapter ^adapter;
DataTable ^dtMain;
private: System::Void Form1_Load(System::Object^ sender, System::EventArgs^ e) {
DBPath = Application::StartupPath + "\\test.mdb";
// create DB via ADOX if not exists
if (!File::Exists(DBPath)) {
ADOX::Catalog ^cat = gcnew ADOX::Catalog();
cat->Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + DBPath);
cat = nullptr;
}
// connect to DB
conn = gcnew OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + DBPath);
conn->Open();
// create table "Table_1" if not exists
// DO NOT USE SPACES IN TABLE AND COLUMNS NAMES TO PREVENT TROUBLES WITH SAVING, USE _
// OLEDBCOMMANDBUILDER DON'T SUPPORT COLUMNS NAMES WITH SPACES
try {
OleDbCommand ^cmd = gcnew OleDbCommand("CREATE TABLE [Table_1] ([id] COUNTER PRIMARY KEY, [text_column] MEMO, [int_column] INT);", conn);
cmd->ExecuteNonQuery();
delete cmd;
} catch (Exception ^ex) {if (ex != nullptr) ex = nullptr; }
// get all tables from DB
DataTable ^dt = conn->GetSchema("Tables");
for (int i = 0; i < dt->Rows->Count; i++) {
if (dt->Rows[i]->ItemArray[dt->Columns->IndexOf("TABLE_TYPE")]->ToString() == "TABLE") {
comboBoxTables->Items->Add(dt->Rows[i]->ItemArray[dt->Columns->IndexOf("TABLE_NAME")]->ToString());
}
}
delete dt;
}
private: System::Void buttonOK_Click(System::Object^ sender, System::EventArgs^ e) {
if (comboBoxTables->SelectedItem == nullptr) return;
adapter = gcnew OleDbDataAdapter("SELECT * FROM [" + comboBoxTables->SelectedItem->ToString() + "]", conn);
gcnew OleDbCommandBuilder(adapter);
dtMain = gcnew DataTable();
adapter->Fill(dtMain);
dtMain->Columns["id"]->ReadOnly = true; // deprecate id field edit to prevent exceptions
dataGridView1->DataSource = dtMain;
}
private: System::Void Form1_FormClosed(System::Object^ sender, System::Windows::Forms::FormClosedEventArgs^ e) {
if (adapter == nullptr) return;
adapter->Update(dtMain);
}
// show tooltip (not intrusive MessageBox) when user trying to input letters into INT column cell
private: System::Void dataGridView1_DataError(System::Object^ sender, System::Windows::Forms::DataGridViewDataErrorEventArgs^ e) {
if (dtMain->Columns[e->ColumnIndex]->DataType == Int64::typeid ||
dtMain->Columns[e->ColumnIndex]->DataType == Int32::typeid ||
dtMain->Columns[e->ColumnIndex]->DataType == Int16::typeid) {
Rectangle ^rectColumn;
rectColumn = dataGridView1->GetColumnDisplayRectangle(e->ColumnIndex, false);
Rectangle ^rectRow;
rectRow = dataGridView1->GetRowDisplayRectangle(e->RowIndex, false);
toolTip1->ToolTipTitle = "This field is for numbers only.";
toolTip1->Show(" ",
dataGridView1,
rectColumn->Left, rectRow->Top + rectRow->Height);
delete rectColumn;
delete rectRow;
}
}
private: System::Void dataGridView1_MouseDown(System::Object^ sender, System::Windows::Forms::MouseEventArgs^ e) {
toolTip1->Hide(dataGridView1);
}
};
}
Access (accdb)
Important:
- (C# and VB.NET only) set the project configuration to x86: 32-bit provider only may be installed
- you can create table with space in it's/column's name by using [] characters (e.g [column name]), but you can't save changes in this table by using OleDbCommandBuilder
- Redistribution: accdb databases aren't requires MS Access to run, but are requires Microsoft Access Database Engine. Download it's 2010 (x86 and x64) version here: http://www.microsoft.com/en-us/download/details.aspx?id=13255
Code in C#:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO; // File.Exists()
using System.Data.OleDb; // OleDbConnection, OleDbDataAdapter, OleDbCommandBuilder
namespace MS_Access__accdb__in_CSharp
{
public partial class Form1 : Form
{
string DBPath;
OleDbConnection conn;
OleDbDataAdapter adapter;
DataTable dtMain;
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
DBPath = Application.StartupPath + "\\test.accdb";
// create DB via ADOX if not exists
if (!File.Exists(DBPath))
{
ADOX.Catalog cat = new ADOX.Catalog();
try
{
cat.Create("Provider=Microsoft.ACE.OLEDB.10.0;Data Source=" + DBPath);
}
catch
{
try
{
cat.Create("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + DBPath);
}
catch
{
try
{
cat.Create("Provider=Microsoft.ACE.OLEDB.14.0;Data Source=" + DBPath);
}
catch
{
cat.Create("Provider=Microsoft.ACE.OLEDB.15.0;Data Source=" + DBPath);
}
}
}
cat = null;
}
// connect to DB
try
{
conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.10.0;Data Source=" + DBPath);
conn.Open();
}
catch
{
try
{
conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + DBPath);
conn.Open();
}
catch
{
try
{
conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.14.0;Data Source=" + DBPath);
conn.Open();
}
catch
{
conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.15.0;Data Source=" + DBPath);
conn.Open();
}
}
}
// create table "Table_1" if not exists
// DO NOT USE SPACES IN TABLE AND COLUMNS NAMES TO PREVENT TROUBLES WITH SAVING, USE _
// OLEDBCOMMANDBUILDER DON'T SUPPORT COLUMNS NAMES WITH SPACES
try
{
using (OleDbCommand cmd = new OleDbCommand("CREATE TABLE [Table_1] ([id] COUNTER PRIMARY KEY, [text_column] MEMO, [int_column] INT);", conn))
{
cmd.ExecuteNonQuery();
}
}
catch (Exception ex) {if (ex != null) ex = null; }
// get all tables from DB
using (DataTable dt = conn.GetSchema("Tables"))
{
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i].ItemArray[dt.Columns.IndexOf("TABLE_TYPE")].ToString() == "TABLE")
{
comboBoxTables.Items.Add(dt.Rows[i].ItemArray[dt.Columns.IndexOf("TABLE_NAME")].ToString());
}
}
}
}
private void buttonOK_Click(object sender, EventArgs e)
{
if (comboBoxTables.SelectedItem == null) return;
adapter = new OleDbDataAdapter("SELECT * FROM [" + comboBoxTables.SelectedItem.ToString() + "]", conn);
new OleDbCommandBuilder(adapter);
dtMain = new DataTable();
adapter.Fill(dtMain);
dtMain.Columns["id"].ReadOnly = true; // deprecate id field edit to prevent exceptions
dataGridView1.DataSource = dtMain;
}
private void Form1_FormClosed(object sender, FormClosedEventArgs e)
{
if (adapter == null) return;
adapter.Update(dtMain);
}
// show tooltip (not intrusive MessageBox) when user trying to input letters into INT column cell
private void dataGridView1_DataError(object sender, DataGridViewDataErrorEventArgs e)
{
if (dtMain.Columns[e.ColumnIndex].DataType == typeof(Int64) ||
dtMain.Columns[e.ColumnIndex].DataType == typeof(Int32) ||
dtMain.Columns[e.ColumnIndex].DataType == typeof(Int16))
{
Rectangle rectColumn;
rectColumn = dataGridView1.GetColumnDisplayRectangle(e.ColumnIndex, false);
Rectangle rectRow;
rectRow = dataGridView1.GetRowDisplayRectangle(e.RowIndex, false);
toolTip1.ToolTipTitle = "This field is for numbers only.";
toolTip1.Show(" ",
dataGridView1,
rectColumn.Left, rectRow.Top + rectRow.Height);
}
}
private void dataGridView1_MouseDown(object sender, MouseEventArgs e)
{
toolTip1.Hide(dataGridView1);
}
}
}
Code in VB.NET:
'=======================================================
'Service provided by Telerik (www.telerik.com)
'Conversion powered by NRefactory.
'Twitter: @telerik
'Facebook: facebook.com/telerik
'=======================================================
Imports System.IO
' File.Exists()
Imports System.Data.OleDb
' OleDbConnection, OleDbDataAdapter, OleDbCommandBuilder
Public Class Form1
Private DBPath As String
Private conn As OleDbConnection
Private adapter As OleDbDataAdapter
Private dtMain As DataTable
Public Sub New()
InitializeComponent()
End Sub
Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs)
DBPath = Application.StartupPath + "\test.accdb"
' create DB via ADOX if not exists
' NOTE: to use ADOX add reference to COM Microsoft ADO Ext. 6.0 for DDL and Security!
If Not File.Exists(DBPath) Then
Dim cat As New ADOX.Catalog()
Try
cat.Create("Provider=Microsoft.ACE.OLEDB.10.0;Data Source=" + DBPath)
Catch
Try
cat.Create("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + DBPath)
Catch
Try
cat.Create("Provider=Microsoft.ACE.OLEDB.14.0;Data Source=" + DBPath)
Catch
cat.Create("Provider=Microsoft.ACE.OLEDB.15.0;Data Source=" + DBPath)
End Try
End Try
End Try
'=======================================================
'Service provided by Telerik (www.telerik.com)
'Conversion powered by NRefactory.
'Twitter: @telerik
'Facebook: facebook.com/telerik
'=======================================================
cat = Nothing
End If
' connect to DB
Try
conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.10.0;Data Source=" + DBPath)
conn.Open()
Catch
Try
conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + DBPath)
conn.Open()
Catch
Try
conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.14.0;Data Source=" + DBPath)
conn.Open()
Catch
conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.15.0;Data Source=" + DBPath)
conn.Open()
End Try
End Try
End Try
'=======================================================
'Service provided by Telerik (www.telerik.com)
'Conversion powered by NRefactory.
'Twitter: @telerik
'Facebook: facebook.com/telerik
'=======================================================
' create table "Table_1" if not exists
' DO NOT USE SPACES IN TABLE AND COLUMNS NAMES TO PREVENT TROUBLES WITH SAVING, USE _
' OLEDBCOMMANDBUILDER DON'T SUPPORT COLUMNS NAMES WITH SPACES
Try
Using cmd As New OleDbCommand("CREATE TABLE [Table_1] ([id] COUNTER PRIMARY KEY, [text_column] MEMO, [int_column] INT);", conn)
cmd.ExecuteNonQuery()
End Using
Catch ex As Exception
If ex IsNot Nothing Then
ex = Nothing
End If
End Try
' get all tables from DB
Using dt As DataTable = conn.GetSchema("Tables")
For i As Integer = 0 To dt.Rows.Count - 1
If dt.Rows(i).ItemArray(dt.Columns.IndexOf("TABLE_TYPE")).ToString() = "TABLE" Then
comboBoxTables.Items.Add(dt.Rows(i).ItemArray(dt.Columns.IndexOf("TABLE_NAME")).ToString())
End If
Next
End Using
End Sub
Private Sub buttonOK_Click(ByVal sender As Object, ByVal e As EventArgs)
If comboBoxTables.SelectedItem Is Nothing Then
Return
End If
adapter = New OleDbDataAdapter("SELECT * FROM [" + comboBoxTables.SelectedItem.ToString() + "]", conn)
Dim builder = New OleDbCommandBuilder(adapter)
dtMain = New DataTable()
adapter.Fill(dtMain)
dtMain.Columns("id").[ReadOnly] = True
' deprecate id field edit to prevent exceptions
dataGridView1.DataSource = dtMain
End Sub
Private Sub Form1_FormClosed(ByVal sender As Object, ByVal e As FormClosedEventArgs)
If adapter Is Nothing Then
Return
End If
adapter.Update(dtMain)
End Sub
' show tooltip (not intrusive MessageBox) when user trying to input letters into INT column cell
Private Sub dataGridView1_DataError(ByVal sender As Object, ByVal e As DataGridViewDataErrorEventArgs)
If dtMain.Columns(e.ColumnIndex).DataType Is GetType(Int64) Or _
dtMain.Columns(e.ColumnIndex).DataType Is GetType(Int32) Or _
dtMain.Columns(e.ColumnIndex).DataType Is GetType(Int16) Then
Dim rectColumn As Rectangle
rectColumn = DataGridView1.GetColumnDisplayRectangle(e.ColumnIndex, False)
Dim rectRow As Rectangle
rectRow = DataGridView1.GetRowDisplayRectangle(e.RowIndex, False)
ToolTip1.ToolTipTitle = "This field is for numbers only."
ToolTip1.Show(" ", DataGridView1, rectColumn.Left, rectRow.Top + rectRow.Height)
End If
End Sub
Private Sub dataGridView1_MouseDown(ByVal sender As Object, ByVal e As MouseEventArgs)
toolTip1.Hide(dataGridView1)
End Sub
'=======================================================
'Service provided by Telerik (www.telerik.com)
'Conversion powered by NRefactory.
'Twitter: @telerik
'Facebook: facebook.com/telerik
'=======================================================
End Class
'=======================================================
'Service provided by Telerik (www.telerik.com)
'Conversion powered by NRefactory.
'Twitter: @telerik
'Facebook: facebook.com/telerik
'=======================================================
Code in C++/CLI:
#pragma once
namespace MSAccessaccdbinCCLI {
using namespace System;
using namespace System::ComponentModel;
using namespace System::Collections;
using namespace System::Windows::Forms;
using namespace System::Data;
using namespace System::Drawing;
using namespace System::IO; // File::Exists()
using namespace System::Data::OleDb; // OleDbConnection, OleDbDataAdapter, OleDbCommandBuilder
/// <summary>
/// Summary for Form1
///
/// WARNING: If you change the name of this class, you will need to change the
/// 'Resource File Name' property for the managed resource compiler tool
/// associated with all .resx files this class depends on. Otherwise,
/// the designers will not be able to interact properly with localized
/// resources associated with this form.
/// </summary>
public ref class Form1 : public System::Windows::Forms::Form
{
public:
Form1(void)
{
InitializeComponent();
//
//TODO: Add the constructor code here
//
}
protected:
/// <summary>
/// Clean up any resources being used.
/// </summary>
~Form1()
{
if (components)
{
delete components;
}
}
private: System::Windows::Forms::DataGridView^ dataGridView1;
private: System::Windows::Forms::Label^ label1;
private: System::Windows::Forms::ComboBox^ comboBoxTables;
private: System::Windows::Forms::Button^ buttonOK;
private: System::Windows::Forms::ToolTip^ toolTip1;
private: System::ComponentModel::IContainer^ components;
protected:
private:
/// <summary>
/// Required designer variable.
/// </summary>
#pragma region Windows Form Designer generated code
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
void InitializeComponent(void)
{
this->components = (gcnew System::ComponentModel::Container());
this->dataGridView1 = (gcnew System::Windows::Forms::DataGridView());
this->label1 = (gcnew System::Windows::Forms::Label());
this->comboBoxTables = (gcnew System::Windows::Forms::ComboBox());
this->buttonOK = (gcnew System::Windows::Forms::Button());
this->toolTip1 = (gcnew System::Windows::Forms::ToolTip(this->components));
(cli::safe_cast<System::ComponentModel::ISupportInitialize^ >(this->dataGridView1))->BeginInit();
this->SuspendLayout();
//
// dataGridView1
//
this->dataGridView1->Anchor = static_cast<System::Windows::Forms::AnchorStyles>((((System::Windows::Forms::AnchorStyles::Top | System::Windows::Forms::AnchorStyles::Bottom)
| System::Windows::Forms::AnchorStyles::Left)
| System::Windows::Forms::AnchorStyles::Right));
this->dataGridView1->ColumnHeadersHeightSizeMode = System::Windows::Forms::DataGridViewColumnHeadersHeightSizeMode::AutoSize;
this->dataGridView1->Location = System::Drawing::Point(12, 44);
this->dataGridView1->Name = L"dataGridView1";
this->dataGridView1->ShowCellToolTips = false;
this->dataGridView1->Size = System::Drawing::Size(493, 266);
this->dataGridView1->TabIndex = 0;
this->dataGridView1->MouseDown += gcnew System::Windows::Forms::MouseEventHandler(this, &Form1::dataGridView1_MouseDown);
this->dataGridView1->DataError += gcnew System::Windows::Forms::DataGridViewDataErrorEventHandler(this, &Form1::dataGridView1_DataError);
//
// label1
//
this->label1->Location = System::Drawing::Point(12, 9);
this->label1->Name = L"label1";
this->label1->Size = System::Drawing::Size(65, 32);
this->label1->TabIndex = 1;
this->label1->Text = L"Select table to show:";
//
// comboBoxTables
//
this->comboBoxTables->DropDownStyle = System::Windows::Forms::ComboBoxStyle::DropDownList;
this->comboBoxTables->FormattingEnabled = true;
this->comboBoxTables->Location = System::Drawing::Point(83, 12);
this->comboBoxTables->Name = L"comboBoxTables";
this->comboBoxTables->Size = System::Drawing::Size(165, 21);
this->comboBoxTables->TabIndex = 2;
//
// buttonOK
//
this->buttonOK->Location = System::Drawing::Point(254, 12);
this->buttonOK->Name = L"buttonOK";
this->buttonOK->Size = System::Drawing::Size(56, 21);
this->buttonOK->TabIndex = 3;
this->buttonOK->Text = L"OK";
this->buttonOK->UseVisualStyleBackColor = true;
this->buttonOK->Click += gcnew System::EventHandler(this, &Form1::buttonOK_Click);
//
// toolTip1
//
this->toolTip1->AutoPopDelay = 5000;
this->toolTip1->InitialDelay = 0;
this->toolTip1->IsBalloon = true;
this->toolTip1->ReshowDelay = 0;
this->toolTip1->ToolTipIcon = System::Windows::Forms::ToolTipIcon::Warning;
this->toolTip1->UseAnimation = false;
this->toolTip1->UseFading = false;
//
// Form1
//
this->AutoScaleDimensions = System::Drawing::SizeF(6, 13);
this->AutoScaleMode = System::Windows::Forms::AutoScaleMode::Font;
this->ClientSize = System::Drawing::Size(517, 322);
this->Controls->Add(this->buttonOK);
this->Controls->Add(this->comboBoxTables);
this->Controls->Add(this->label1);
this->Controls->Add(this->dataGridView1);
this->Name = L"Form1";
this->Text = L"Form1";
this->Load += gcnew System::EventHandler(this, &Form1::Form1_Load);
this->FormClosed += gcnew System::Windows::Forms::FormClosedEventHandler(this, &Form1::Form1_FormClosed);
(cli::safe_cast<System::ComponentModel::ISupportInitialize^ >(this->dataGridView1))->EndInit();
this->ResumeLayout(false);
}
#pragma endregion
String ^DBPath;
OleDbConnection ^conn;
OleDbDataAdapter ^adapter;
DataTable ^dtMain;
private: System::Void Form1_Load(System::Object^ sender, System::EventArgs^ e) {
DBPath = Application::StartupPath + "\\test.accdb";
// create DB via ADOX if not exists
if (!File::Exists(DBPath)) {
ADOX::Catalog ^cat = gcnew ADOX::Catalog();
try {
cat->Create("Provider=Microsoft.ACE.OLEDB.10.0;Data Source=" + DBPath);
}
catch (Exception ^ex) {
try {
cat->Create("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + DBPath);
}
catch (Exception ^ex) {
try {
cat->Create("Provider=Microsoft.ACE.OLEDB.14.0;Data Source=" + DBPath);
}
catch (Exception ^ex) {
cat->Create("Provider=Microsoft.ACE.OLEDB.15.0;Data Source=" + DBPath);
}
}
}
cat = nullptr;
}
// connect to DB
try {
conn = gcnew OleDbConnection("Provider=Microsoft.ACE.OLEDB.10.0;Data Source=" + DBPath);
conn->Open();
}
catch (Exception ^ex)
{
try {
conn = gcnew OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + DBPath);
conn->Open();
}
catch (Exception ^ex) {
try {
conn = gcnew OleDbConnection("Provider=Microsoft.ACE.OLEDB.14.0;Data Source=" + DBPath);
conn->Open();
}
catch (Exception ^ex) {
conn = gcnew OleDbConnection("Provider=Microsoft.ACE.OLEDB.15.0;Data Source=" + DBPath);
conn->Open();
}
}
}
// create table "Table_1" if not exists
// DO NOT USE SPACES IN TABLE AND COLUMNS NAMES TO PREVENT TROUBLES WITH SAVING, USE _
// OLEDBCOMMANDBUILDER DON'T SUPPORT COLUMNS NAMES WITH SPACES
try {
OleDbCommand ^cmd = gcnew OleDbCommand("CREATE TABLE [Table_1] ([id] COUNTER PRIMARY KEY, [text_column] MEMO, [int_column] INT);", conn);
cmd->ExecuteNonQuery();
delete cmd;
} catch (Exception ^ex) {if (ex != nullptr) ex = nullptr; }
// get all tables from DB
DataTable ^dt = conn->GetSchema("Tables");
for (int i = 0; i < dt->Rows->Count; i++) {
if (dt->Rows[i]->ItemArray[dt->Columns->IndexOf("TABLE_TYPE")]->ToString() == "TABLE") {
comboBoxTables->Items->Add(dt->Rows[i]->ItemArray[dt->Columns->IndexOf("TABLE_NAME")]->ToString());
}
}
delete dt;
}
private: System::Void buttonOK_Click(System::Object^ sender, System::EventArgs^ e) {
if (comboBoxTables->SelectedItem == nullptr) return;
adapter = gcnew OleDbDataAdapter("SELECT * FROM [" + comboBoxTables->SelectedItem->ToString() + "]", conn);
gcnew OleDbCommandBuilder(adapter);
dtMain = gcnew DataTable();
adapter->Fill(dtMain);
dtMain->Columns["id"]->ReadOnly = true; // deprecate id field edit to prevent exceptions
dataGridView1->DataSource = dtMain;
}
private: System::Void Form1_FormClosed(System::Object^ sender, System::Windows::Forms::FormClosedEventArgs^ e) {
if (adapter == nullptr) return;
adapter->Update(dtMain);
}
// show tooltip (not intrusive MessageBox) when user trying to input letters into INT column cell
private: System::Void dataGridView1_DataError(System::Object^ sender, System::Windows::Forms::DataGridViewDataErrorEventArgs^ e) {
if (dtMain->Columns[e->ColumnIndex]->DataType == Int64::typeid ||
dtMain->Columns[e->ColumnIndex]->DataType == Int32::typeid ||
dtMain->Columns[e->ColumnIndex]->DataType == Int16::typeid) {
Rectangle ^rectColumn;
rectColumn = dataGridView1->GetColumnDisplayRectangle(e->ColumnIndex, false);
Rectangle ^rectRow;
rectRow = dataGridView1->GetRowDisplayRectangle(e->RowIndex, false);
toolTip1->ToolTipTitle = "This field is for numbers only.";
toolTip1->Show(" ",
dataGridView1,
rectColumn->Left, rectRow->Top + rectRow->Height);
delete rectColumn;
delete rectRow;
}
}
private: System::Void dataGridView1_MouseDown(System::Object^ sender, System::Windows::Forms::MouseEventArgs^ e) {
toolTip1->Hide(dataGridView1);
}
};
}
MySQL
Important:
- Redistribution: MySql.Data.dll library may be copied into folder with exe. You must add a reference to it for use it in your project.
Code in C#:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
namespace MySQL_in_CSharp
{
public partial class Form1 : Form
{
string DBName;
MySqlConnection conn;
MySqlDataAdapter adapter;
DataTable dtMain;
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
DBName = "test";
// connect to server
// IF NOT WORKS: type your server name, user name (Uid) and password (Pwd)
// and run a server
conn = new MySqlConnection("Server=localhost;Uid=root;Pwd=;");
conn.Open();
// create a db if not exists
try
{
using (MySqlCommand cmd = new MySqlCommand("CREATE DATABASE " + DBName, conn))
{
cmd.ExecuteNonQuery();
}
}
catch { }
// select our database
using (MySqlCommand cmd = new MySqlCommand("USE " + DBName, conn))
{
cmd.ExecuteNonQuery();
}
// create table "Table 1" if not exists
// WARNING: you can to use tables and columns names with spaces,
// but, you must use SPECIAL quote
// it's "`", not "'"!!!
using (MySqlCommand cmd = new MySqlCommand("CREATE TABLE IF NOT EXISTS `Table 1` (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, `text col` TEXT, `int col` INT);", conn))
{
cmd.ExecuteNonQuery();
}
// get all tables from DB
using (DataTable dt = conn.GetSchema("Tables"))
{
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i].ItemArray[dt.Columns.IndexOf("TABLE_TYPE")].ToString() == "BASE TABLE" &&
dt.Rows[i].ItemArray[dt.Columns.IndexOf("TABLE_SCHEMA")].ToString() == DBName)
{
comboBoxTables.Items.Add(dt.Rows[i].ItemArray[dt.Columns.IndexOf("TABLE_NAME")].ToString());
}
}
}
}
private void buttonOK_Click(object sender, EventArgs e)
{
adapter = new MySqlDataAdapter("SELECT * FROM `" + comboBoxTables.SelectedItem.ToString() + "`", conn);
new MySqlCommandBuilder(adapter);
dtMain = new DataTable();
adapter.Fill(dtMain);
dtMain.Columns["id"].ReadOnly = true; // deprecate id field edit to prevent exceptions
dataGridView1.DataSource = dtMain;
}
private void Form1_FormClosed(object sender, FormClosedEventArgs e)
{
if (adapter == null) return;
adapter.Update(dtMain);
}
// show tooltip (not intrusive MessageBox) when user trying to input letters into INT column cell
private void dataGridView1_DataError(object sender, DataGridViewDataErrorEventArgs e)
{
if (dtMain.Columns[e.ColumnIndex].DataType == typeof(Int64) ||
dtMain.Columns[e.ColumnIndex].DataType == typeof(Int32) ||
dtMain.Columns[e.ColumnIndex].DataType == typeof(Int16))
{
Rectangle rectColumn;
rectColumn = dataGridView1.GetColumnDisplayRectangle(e.ColumnIndex, false);
Rectangle rectRow;
rectRow = dataGridView1.GetRowDisplayRectangle(e.RowIndex, false);
toolTip1.ToolTipTitle = "This field is for numbers only.";
toolTip1.Show(" ",
dataGridView1,
rectColumn.Left, rectRow.Top + rectRow.Height);
}
}
private void dataGridView1_MouseDown(object sender, MouseEventArgs e)
{
toolTip1.Hide(dataGridView1);
}
}
}
Code in VB.NET:
'=======================================================
'Service provided by Telerik (www.telerik.com)
'Conversion powered by NRefactory.
'Twitter: @telerik
'Facebook: facebook.com/telerik
'=======================================================
Imports MySql.Data.MySqlClient
' MySqlConnection, MySqlDataAdapter, MySqlCommandBuilder
Public Class Form1
Private DBName As String
Private conn As MySqlConnection
Private adapter As MySqlDataAdapter
Private dtMain As DataTable
Public Sub New()
InitializeComponent()
End Sub
Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs)
DBName = "test"
' connect to server
' IF NOT WORKS: type your server name, user name (Uid) and password (Pwd)
' and run a server
conn = New MySqlConnection("Server=localhost;Uid=root;Pwd=;")
conn.Open()
' create a db if not exists
Try
Using cmd As New MySqlCommand(Convert.ToString("CREATE DATABASE ") & DBName, conn)
cmd.ExecuteNonQuery()
End Using
Catch
End Try
' select our database
Using cmd As New MySqlCommand(Convert.ToString("USE ") & DBName, conn)
cmd.ExecuteNonQuery()
End Using
' create table "Table 1" if not exists
' WARNING: you can to use tables and columns names with spaces,
' but, you must use SPECIAL quote
' it's "`", not "'"!!!
Using cmd As New MySqlCommand("CREATE TABLE IF NOT EXISTS `Table 1` (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, `text col` TEXT, `int col` INT);", conn)
cmd.ExecuteNonQuery()
End Using
' get all tables from DB
Using dt As DataTable = conn.GetSchema("Tables")
For i As Integer = 0 To dt.Rows.Count - 1
If dt.Rows(i).ItemArray(dt.Columns.IndexOf("TABLE_TYPE")).ToString() = "BASE TABLE" AndAlso dt.Rows(i).ItemArray(dt.Columns.IndexOf("TABLE_SCHEMA")).ToString() = DBName Then
comboBoxTables.Items.Add(dt.Rows(i).ItemArray(dt.Columns.IndexOf("TABLE_NAME")).ToString())
End If
Next
End Using
End Sub
Private Sub buttonOK_Click(ByVal sender As Object, ByVal e As EventArgs)
adapter = New MySqlDataAdapter("SELECT * FROM `" + comboBoxTables.SelectedItem.ToString() + "`", conn)
Dim builder = New MySqlCommandBuilder(adapter)
dtMain = New DataTable()
adapter.Fill(dtMain)
dtMain.Columns("id").[ReadOnly] = True
' deprecate id field edit to prevent exceptions
dataGridView1.DataSource = dtMain
End Sub
Private Sub Form1_FormClosed(ByVal sender As Object, ByVal e As FormClosedEventArgs)
If adapter Is Nothing Then
Return
End If
adapter.Update(dtMain)
End Sub
' show tooltip (not intrusive MessageBox) when user trying to input letters into INT column cell
Private Sub dataGridView1_DataError(ByVal sender As Object, ByVal e As DataGridViewDataErrorEventArgs)
If dtMain.Columns(e.ColumnIndex).DataType Is GetType(Int64) Or _
dtMain.Columns(e.ColumnIndex).DataType Is GetType(Int32) Or _
dtMain.Columns(e.ColumnIndex).DataType Is GetType(Int16) Then
Dim rectColumn As Rectangle
rectColumn = DataGridView1.GetColumnDisplayRectangle(e.ColumnIndex, False)
Dim rectRow As Rectangle
rectRow = DataGridView1.GetRowDisplayRectangle(e.RowIndex, False)
ToolTip1.ToolTipTitle = "This field is for numbers only."
ToolTip1.Show(" ", DataGridView1, rectColumn.Left, rectRow.Top + rectRow.Height)
End If
End Sub
Private Sub dataGridView1_MouseDown(ByVal sender As Object, ByVal e As MouseEventArgs)
toolTip1.Hide(dataGridView1)
End Sub
'=======================================================
'Service provided by Telerik (www.telerik.com)
'Conversion powered by NRefactory.
'Twitter: @telerik
'Facebook: facebook.com/telerik
'=======================================================
End Class
'=======================================================
'Service provided by Telerik (www.telerik.com)
'Conversion powered by NRefactory.
'Twitter: @telerik
'Facebook: facebook.com/telerik
'=======================================================
Code in C++/CLI:
#pragma once
namespace MySQLinCCLI {
using namespace System;
using namespace System::ComponentModel;
using namespace System::Collections;
using namespace System::Windows::Forms;
using namespace System::Data;
using namespace System::Drawing;
using namespace MySql::Data::MySqlClient;
/// <summary>
/// Summary for Form1
///
/// WARNING: If you change the name of this class, you will need to change the
/// 'Resource File Name' property for the managed resource compiler tool
/// associated with all .resx files this class depends on. Otherwise,
/// the designers will not be able to interact properly with localized
/// resources associated with this form.
/// </summary>
public ref class Form1 : public System::Windows::Forms::Form
{
public:
Form1(void)
{
InitializeComponent();
//
//TODO: Add the constructor code here
//
}
protected:
/// <summary>
/// Clean up any resources being used.
/// </summary>
~Form1()
{
if (components)
{
delete components;
}
}
private: System::Windows::Forms::DataGridView^ dataGridView1;
private: System::Windows::Forms::Label^ label1;
private: System::Windows::Forms::ComboBox^ comboBoxTables;
private: System::Windows::Forms::Button^ buttonOK;
private: System::Windows::Forms::ToolTip^ toolTip1;
private: System::ComponentModel::IContainer^ components;
protected:
private:
/// <summary>
/// Required designer variable.
/// </summary>
#pragma region Windows Form Designer generated code
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
void InitializeComponent(void)
{
this->components = (gcnew System::ComponentModel::Container());
this->dataGridView1 = (gcnew System::Windows::Forms::DataGridView());
this->label1 = (gcnew System::Windows::Forms::Label());
this->comboBoxTables = (gcnew System::Windows::Forms::ComboBox());
this->buttonOK = (gcnew System::Windows::Forms::Button());
this->toolTip1 = (gcnew System::Windows::Forms::ToolTip(this->components));
(cli::safe_cast<System::ComponentModel::ISupportInitialize^ >(this->dataGridView1))->BeginInit();
this->SuspendLayout();
//
// dataGridView1
//
this->dataGridView1->Anchor = static_cast<System::Windows::Forms::AnchorStyles>((((System::Windows::Forms::AnchorStyles::Top | System::Windows::Forms::AnchorStyles::Bottom)
| System::Windows::Forms::AnchorStyles::Left)
| System::Windows::Forms::AnchorStyles::Right));
this->dataGridView1->ColumnHeadersHeightSizeMode = System::Windows::Forms::DataGridViewColumnHeadersHeightSizeMode::AutoSize;
this->dataGridView1->Location = System::Drawing::Point(12, 44);
this->dataGridView1->Name = L"dataGridView1";
this->dataGridView1->ShowCellToolTips = false;
this->dataGridView1->Size = System::Drawing::Size(493, 266);
this->dataGridView1->TabIndex = 0;
this->dataGridView1->MouseDown += gcnew System::Windows::Forms::MouseEventHandler(this, &Form1::dataGridView1_MouseDown);
this->dataGridView1->DataError += gcnew System::Windows::Forms::DataGridViewDataErrorEventHandler(this, &Form1::dataGridView1_DataError);
//
// label1
//
this->label1->Location = System::Drawing::Point(12, 9);
this->label1->Name = L"label1";
this->label1->Size = System::Drawing::Size(65, 32);
this->label1->TabIndex = 1;
this->label1->Text = L"Select table to show:";
//
// comboBoxTables
//
this->comboBoxTables->DropDownStyle = System::Windows::Forms::ComboBoxStyle::DropDownList;
this->comboBoxTables->FormattingEnabled = true;
this->comboBoxTables->Location = System::Drawing::Point(83, 12);
this->comboBoxTables->Name = L"comboBoxTables";
this->comboBoxTables->Size = System::Drawing::Size(165, 21);
this->comboBoxTables->TabIndex = 2;
//
// buttonOK
//
this->buttonOK->Location = System::Drawing::Point(254, 12);
this->buttonOK->Name = L"buttonOK";
this->buttonOK->Size = System::Drawing::Size(56, 21);
this->buttonOK->TabIndex = 3;
this->buttonOK->Text = L"OK";
this->buttonOK->UseVisualStyleBackColor = true;
this->buttonOK->Click += gcnew System::EventHandler(this, &Form1::buttonOK_Click);
//
// toolTip1
//
this->toolTip1->AutoPopDelay = 5000;
this->toolTip1->InitialDelay = 0;
this->toolTip1->IsBalloon = true;
this->toolTip1->ReshowDelay = 0;
this->toolTip1->ToolTipIcon = System::Windows::Forms::ToolTipIcon::Warning;
this->toolTip1->UseAnimation = false;
this->toolTip1->UseFading = false;
//
// Form1
//
this->AutoScaleDimensions = System::Drawing::SizeF(6, 13);
this->AutoScaleMode = System::Windows::Forms::AutoScaleMode::Font;
this->ClientSize = System::Drawing::Size(517, 322);
this->Controls->Add(this->buttonOK);
this->Controls->Add(this->comboBoxTables);
this->Controls->Add(this->label1);
this->Controls->Add(this->dataGridView1);
this->Name = L"Form1";
this->Text = L"Form1";
this->Load += gcnew System::EventHandler(this, &Form1::Form1_Load);
this->FormClosed += gcnew System::Windows::Forms::FormClosedEventHandler(this, &Form1::Form1_FormClosed);
(cli::safe_cast<System::ComponentModel::ISupportInitialize^ >(this->dataGridView1))->EndInit();
this->ResumeLayout(false);
}
#pragma endregion
String ^DBName;
MySqlConnection ^conn;
MySqlDataAdapter ^adapter;
DataTable ^dtMain;
private: System::Void Form1_Load(System::Object^ sender, System::EventArgs^ e) {
DBName = "test";
// connect to server
// IF NOT WORKS: type your server name, user name (Uid) and password (Pwd)
// and run a server
conn = gcnew MySqlConnection("Server=localhost;Uid=root;Pwd=;");
conn->Open();
// create a db if not exists
try {
MySqlCommand ^cmd = gcnew MySqlCommand("CREATE DATABASE " + DBName, conn);
cmd->ExecuteNonQuery();
delete cmd;
}
catch (Exception ^ex) { }
// select our database
MySqlCommand ^cmd = gcnew MySqlCommand("USE " + DBName, conn);
cmd->ExecuteNonQuery();
delete cmd;
// create table "Table 1" if not exists
// WARNING: you can to use tables and columns names with spaces,
// but, you must use SPECIAL quote
// it's "`", not "'"!!!
cmd = gcnew MySqlCommand("CREATE TABLE IF NOT EXISTS `Table 1` (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, `text col` TEXT, `int col` INT);", conn);
cmd->ExecuteNonQuery();
delete cmd;
// get all tables from DB
DataTable ^dt = conn->GetSchema("Tables");
for (int i = 0; i < dt->Rows->Count; i++) {
if (dt->Rows[i]->ItemArray[dt->Columns->IndexOf("TABLE_TYPE")]->ToString() == "BASE TABLE" &&
dt->Rows[i]->ItemArray[dt->Columns->IndexOf("TABLE_SCHEMA")]->ToString() == DBName) {
comboBoxTables->Items->Add(dt->Rows[i]->ItemArray[dt->Columns->IndexOf("TABLE_NAME")]->ToString());
}
}
delete dt;
}
private: System::Void buttonOK_Click(System::Object^ sender, System::EventArgs^ e) {
if (comboBoxTables->SelectedItem == nullptr) return;
adapter = gcnew MySqlDataAdapter("SELECT * FROM `" + comboBoxTables->SelectedItem->ToString() + "`", conn);
gcnew MySqlCommandBuilder(adapter);
dtMain = gcnew DataTable();
adapter->Fill(dtMain);
dtMain->Columns["id"]->ReadOnly = true; // deprecate id field edit to prevent exceptions
dataGridView1->DataSource = dtMain;
}
private: System::Void Form1_FormClosed(System::Object^ sender, System::Windows::Forms::FormClosedEventArgs^ e) {
if (adapter == nullptr) return;
adapter->Update(dtMain);
}
// show tooltip (not intrusive MessageBox) when user trying to input letters into INT column cell
private: System::Void dataGridView1_DataError(System::Object^ sender, System::Windows::Forms::DataGridViewDataErrorEventArgs^ e) {
if (dtMain->Columns[e->ColumnIndex]->DataType == Int64::typeid ||
dtMain->Columns[e->ColumnIndex]->DataType == Int32::typeid ||
dtMain->Columns[e->ColumnIndex]->DataType == Int16::typeid) {
Rectangle ^rectColumn;
rectColumn = dataGridView1->GetColumnDisplayRectangle(e->ColumnIndex, false);
Rectangle ^rectRow;
rectRow = dataGridView1->GetRowDisplayRectangle(e->RowIndex, false);
toolTip1->ToolTipTitle = "This field is for numbers only.";
toolTip1->Show(" ",
dataGridView1,
rectColumn->Left, rectRow->Top + rectRow->Height);
delete rectColumn;
delete rectRow;
}
}
private: System::Void dataGridView1_MouseDown(System::Object^ sender, System::Windows::Forms::MouseEventArgs^ e) {
toolTip1->Hide(dataGridView1);
}
};
}
SQLite
Important:
- (C# and VB.NET only) set project configuration to x86.
- Redistribution: System.Data.SQLite.dll and SQL.Interop.dll libraries must be copied into folder with exe. You must add a reference to it for use it in your project.
Code in C#:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SQLite;
namespace SQLite_in_CSharp
{
public partial class Form1 : Form
{
string DBPath;
SQLiteConnection conn;
SQLiteDataAdapter adapter;
DataTable dtMain;
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
DBPath = Application.StartupPath + "\\test.db";
// connect to DB
// it will be created automatically if not exists
conn = new SQLiteConnection("Data Source=" + DBPath);
conn.Open();
// create table "Table 1" if not exists
// YOU CAN USE SPACES IN TABLE AND COLUMNS NAMES
// BUT USE [] WITH TABLES AND '' WITH COLUMNS
//
// ALSO, SQLITE SUPPORTS "CREATE TABLE IF NOT EXISTS"
using (SQLiteCommand cmd = new SQLiteCommand("CREATE TABLE IF NOT EXISTS [Table 1] (id INTEGER PRIMARY KEY AUTOINCREMENT, 'text column' TEXT, 'int column' INTEGER);", conn))
{
cmd.ExecuteNonQuery();
}
// get all tables from DB
using (DataTable dt = conn.GetSchema("Tables"))
{
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i].ItemArray[dt.Columns.IndexOf("TABLE_TYPE")].ToString() == "table")
{
comboBoxTables.Items.Add(dt.Rows[i].ItemArray[dt.Columns.IndexOf("TABLE_NAME")].ToString());
}
}
}
}
private void buttonOK_Click(object sender, EventArgs e)
{
if (comboBoxTables.SelectedItem == null) return;
adapter = new SQLiteDataAdapter("SELECT * FROM [" + comboBoxTables.SelectedItem.ToString() + "]", conn);
new SQLiteCommandBuilder(adapter);
dtMain = new DataTable();
adapter.Fill(dtMain);
dtMain.Columns["id"].ReadOnly = true; // deprecate id field edit to prevent exceptions
dataGridView1.DataSource = dtMain;
}
private void Form1_FormClosed(object sender, FormClosedEventArgs e)
{
if (adapter == null) return;
adapter.Update(dtMain);
}
// show tooltip (not intrusive MessageBox) when user trying to input letters into INT column cell
private void dataGridView1_DataError(object sender, DataGridViewDataErrorEventArgs e)
{
if (dtMain.Columns[e.ColumnIndex].DataType == typeof(Int64) ||
dtMain.Columns[e.ColumnIndex].DataType == typeof(Int32) ||
dtMain.Columns[e.ColumnIndex].DataType == typeof(Int16))
{
Rectangle rectColumn;
rectColumn = dataGridView1.GetColumnDisplayRectangle(e.ColumnIndex, false);
Rectangle rectRow;
rectRow = dataGridView1.GetRowDisplayRectangle(e.RowIndex, false);
toolTip1.ToolTipTitle = "This field is for numbers only.";
toolTip1.Show(" ",
dataGridView1,
rectColumn.Left, rectRow.Top + rectRow.Height);
}
}
private void dataGridView1_MouseDown(object sender, MouseEventArgs e)
{
toolTip1.Hide(dataGridView1);
}
}
}
Code in VB.NET:
'=======================================================
'Service provided by Telerik (www.telerik.com)
'Conversion powered by NRefactory.
'Twitter: @telerik
'Facebook: facebook.com/telerik
'=======================================================
Imports System.Data.SQLite ' SQLiteConnection, SQLiteDataAdapter, SQLiteCommandBuilder
' WARNING: Add a reference to System.Data.SQLite
' redistributable with System.Data.SQLite.dll and SQLite.Interop.dll
Public Class Form1
Dim DBPath As String
Dim conn As SQLiteConnection
Dim adapter As SQLiteDataAdapter
Dim dtMain As DataTable
'=======================================================
'Service provided by Telerik (www.telerik.com)
'Conversion powered by NRefactory.
'Twitter: @telerik
'Facebook: facebook.com/telerik
'=======================================================
Public Sub New()
InitializeComponent()
End Sub
Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs)
DBPath = Application.StartupPath + "\test.db"
' connect to DB
' it will be created automatically if not exists
conn = New SQLiteConnection("Data Source=" + DBPath)
conn.Open()
' create table "Table 1" if not exists
' YOU CAN USE SPACES IN TABLE AND COLUMNS NAMES
' BUT USE [] WITH TABLES AND '' WITH COLUMNS
'
' ALSO, SQLITE SUPPORTS "CREATE TABLE IF NOT EXISTS"
Using cmd As New SQLiteCommand("CREATE TABLE IF NOT EXISTS [Table 1] (id INTEGER PRIMARY KEY AUTOINCREMENT, 'text column' TEXT, 'int column' INTEGER);", conn)
cmd.ExecuteNonQuery()
End Using
' get all tables from DB
Using dt As DataTable = conn.GetSchema("Tables")
For i As Integer = 0 To dt.Rows.Count - 1
If dt.Rows(i).ItemArray(dt.Columns.IndexOf("TABLE_TYPE")).ToString() = "table" Then
ComboBoxTables.Items.Add(dt.Rows(i).ItemArray(dt.Columns.IndexOf("TABLE_NAME")).ToString())
End If
Next
End Using
'=======================================================
'Service provided by Telerik (www.telerik.com)
'Conversion powered by NRefactory.
'Twitter: @telerik
'Facebook: facebook.com/telerik
'=======================================================
End Sub
Private Sub buttonOK_Click(ByVal sender As Object, ByVal e As EventArgs)
If ComboBoxTables.SelectedItem Is Nothing Then
Return
End If
adapter = New SQLiteDataAdapter("SELECT * FROM [" + ComboBoxTables.SelectedItem.ToString() + "]", conn)
Dim builder = New SQLiteCommandBuilder(adapter)
dtMain = New DataTable()
adapter.Fill(dtMain)
dtMain.Columns("id").[ReadOnly] = True
' deprecate id field edit to prevent exceptions
DataGridView1.DataSource = dtMain
'=======================================================
'Service provided by Telerik (www.telerik.com)
'Conversion powered by NRefactory.
'Twitter: @telerik
'Facebook: facebook.com/telerik
'=======================================================
End Sub
Private Sub Form1_FormClosed(ByVal sender As Object, ByVal e As FormClosedEventArgs)
If adapter Is Nothing Then
Return
End If
adapter.Update(dtMain)
End Sub
' show tooltip (not intrusive MessageBox) when user trying to input letters into INT column cell
Private Sub dataGridView1_DataError(ByVal sender As Object, ByVal e As DataGridViewDataErrorEventArgs)
If dtMain.Columns(e.ColumnIndex).DataType Is GetType(Int64) Or _
dtMain.Columns(e.ColumnIndex).DataType Is GetType(Int32) Or _
dtMain.Columns(e.ColumnIndex).DataType Is GetType(Int16) Then
Dim rectColumn As Rectangle
rectColumn = DataGridView1.GetColumnDisplayRectangle(e.ColumnIndex, False)
Dim rectRow As Rectangle
rectRow = DataGridView1.GetRowDisplayRectangle(e.RowIndex, False)
ToolTip1.ToolTipTitle = "This field is for numbers only."
ToolTip1.Show(" ", DataGridView1, rectColumn.Left, rectRow.Top + rectRow.Height)
End If
End Sub
Private Sub dataGridView1_MouseDown(ByVal sender As Object, ByVal e As MouseEventArgs)
toolTip1.Hide(dataGridView1)
End Sub
'=======================================================
'Service provided by Telerik (www.telerik.com)
'Conversion powered by NRefactory.
'Twitter: @telerik
'Facebook: facebook.com/telerik
'=======================================================
End Class
'=======================================================
'Service provided by Telerik (www.telerik.com)
'Conversion powered by NRefactory.
'Twitter: @telerik
'Facebook: facebook.com/telerik
'=======================================================
Code in C++/CLI:
#pragma once
namespace SQLiteinCCLI {
using namespace System;
using namespace System::ComponentModel;
using namespace System::Collections;
using namespace System::Windows::Forms;
using namespace System::Data;
using namespace System::Drawing;
using namespace System::Data::SQLite;
/// <summary>
/// Summary for Form1
///
/// WARNING: If you change the name of this class, you will need to change the
/// 'Resource File Name' property for the managed resource compiler tool
/// associated with all .resx files this class depends on. Otherwise,
/// the designers will not be able to interact properly with localized
/// resources associated with this form.
/// </summary>
public ref class Form1 : public System::Windows::Forms::Form
{
public:
Form1(void)
{
InitializeComponent();
//
//TODO: Add the constructor code here
//
}
protected:
/// <summary>
/// Clean up any resources being used.
/// </summary>
~Form1()
{
if (components)
{
delete components;
}
}
private: System::Windows::Forms::DataGridView^ dataGridView1;
private: System::Windows::Forms::Label^ label1;
private: System::Windows::Forms::ComboBox^ comboBoxTables;
private: System::Windows::Forms::Button^ buttonOK;
private: System::Windows::Forms::ToolTip^ toolTip1;
private: System::ComponentModel::IContainer^ components;
protected:
private:
/// <summary>
/// Required designer variable.
/// </summary>
#pragma region Windows Form Designer generated code
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
void InitializeComponent(void)
{
this->components = (gcnew System::ComponentModel::Container());
this->dataGridView1 = (gcnew System::Windows::Forms::DataGridView());
this->label1 = (gcnew System::Windows::Forms::Label());
this->comboBoxTables = (gcnew System::Windows::Forms::ComboBox());
this->buttonOK = (gcnew System::Windows::Forms::Button());
this->toolTip1 = (gcnew System::Windows::Forms::ToolTip(this->components));
(cli::safe_cast<System::ComponentModel::ISupportInitialize^ >(this->dataGridView1))->BeginInit();
this->SuspendLayout();
//
// dataGridView1
//
this->dataGridView1->Anchor = static_cast<System::Windows::Forms::AnchorStyles>((((System::Windows::Forms::AnchorStyles::Top | System::Windows::Forms::AnchorStyles::Bottom)
| System::Windows::Forms::AnchorStyles::Left)
| System::Windows::Forms::AnchorStyles::Right));
this->dataGridView1->ColumnHeadersHeightSizeMode = System::Windows::Forms::DataGridViewColumnHeadersHeightSizeMode::AutoSize;
this->dataGridView1->Location = System::Drawing::Point(12, 44);
this->dataGridView1->Name = L"dataGridView1";
this->dataGridView1->ShowCellToolTips = false;
this->dataGridView1->Size = System::Drawing::Size(493, 266);
this->dataGridView1->TabIndex = 0;
this->dataGridView1->MouseDown += gcnew System::Windows::Forms::MouseEventHandler(this, &Form1::dataGridView1_MouseDown);
this->dataGridView1->DataError += gcnew System::Windows::Forms::DataGridViewDataErrorEventHandler(this, &Form1::dataGridView1_DataError);
//
// label1
//
this->label1->Location = System::Drawing::Point(12, 9);
this->label1->Name = L"label1";
this->label1->Size = System::Drawing::Size(65, 32);
this->label1->TabIndex = 1;
this->label1->Text = L"Select table to show:";
//
// comboBoxTables
//
this->comboBoxTables->DropDownStyle = System::Windows::Forms::ComboBoxStyle::DropDownList;
this->comboBoxTables->FormattingEnabled = true;
this->comboBoxTables->Location = System::Drawing::Point(83, 12);
this->comboBoxTables->Name = L"comboBoxTables";
this->comboBoxTables->Size = System::Drawing::Size(165, 21);
this->comboBoxTables->TabIndex = 2;
//
// buttonOK
//
this->buttonOK->Location = System::Drawing::Point(254, 12);
this->buttonOK->Name = L"buttonOK";
this->buttonOK->Size = System::Drawing::Size(56, 21);
this->buttonOK->TabIndex = 3;
this->buttonOK->Text = L"OK";
this->buttonOK->UseVisualStyleBackColor = true;
this->buttonOK->Click += gcnew System::EventHandler(this, &Form1::buttonOK_Click);
//
// toolTip1
//
this->toolTip1->AutoPopDelay = 5000;
this->toolTip1->InitialDelay = 0;
this->toolTip1->IsBalloon = true;
this->toolTip1->ReshowDelay = 0;
this->toolTip1->ToolTipIcon = System::Windows::Forms::ToolTipIcon::Warning;
this->toolTip1->UseAnimation = false;
this->toolTip1->UseFading = false;
//
// Form1
//
this->AutoScaleDimensions = System::Drawing::SizeF(6, 13);
this->AutoScaleMode = System::Windows::Forms::AutoScaleMode::Font;
this->ClientSize = System::Drawing::Size(517, 322);
this->Controls->Add(this->buttonOK);
this->Controls->Add(this->comboBoxTables);
this->Controls->Add(this->label1);
this->Controls->Add(this->dataGridView1);
this->Name = L"Form1";
this->Text = L"Form1";
this->Load += gcnew System::EventHandler(this, &Form1::Form1_Load);
this->FormClosed += gcnew System::Windows::Forms::FormClosedEventHandler(this, &Form1::Form1_FormClosed);
(cli::safe_cast<System::ComponentModel::ISupportInitialize^ >(this->dataGridView1))->EndInit();
this->ResumeLayout(false);
}
#pragma endregion
String ^DBPath;
SQLiteConnection ^conn;
SQLiteDataAdapter ^adapter;
DataTable ^dtMain;
private: System::Void Form1_Load(System::Object^ sender, System::EventArgs^ e) {
DBPath = Application::StartupPath + "\\test.db";
// connect to DB
// it will be created automatically if not exists
conn = gcnew SQLiteConnection("Data Source=" + DBPath);
conn->Open();
// create table "Table 1" if not exists
// YOU CAN USE SPACES IN TABLE AND COLUMNS NAMES
// BUT USE [] WITH TABLES AND '' WITH COLUMNS
//
// ALSO, SQLITE SUPPORTS "CREATE TABLE IF NOT EXISTS"
SQLiteCommand ^cmd = gcnew SQLiteCommand("CREATE TABLE IF NOT EXISTS [Table 1] (id INTEGER PRIMARY KEY AUTOINCREMENT, 'text column' TEXT, 'int column' INTEGER);", conn);
cmd->ExecuteNonQuery();
delete cmd;
// get all tables from DB
DataTable ^dt = conn->GetSchema("Tables");
for (int i = 0; i < dt->Rows->Count; i++)
{
if (dt->Rows[i]->ItemArray[dt->Columns->IndexOf("TABLE_TYPE")]->ToString() == "table")
{
comboBoxTables->Items->Add(dt->Rows[i]->ItemArray[dt->Columns->IndexOf("TABLE_NAME")]->ToString());
}
}
delete dt;
}
private: System::Void buttonOK_Click(System::Object^ sender, System::EventArgs^ e) {
if (comboBoxTables->SelectedItem == nullptr) return;
adapter = gcnew SQLiteDataAdapter("SELECT * FROM [" + comboBoxTables->SelectedItem->ToString() + "]", conn);
gcnew SQLiteCommandBuilder(adapter);
dtMain = gcnew DataTable();
adapter->Fill(dtMain);
dtMain->Columns["id"]->ReadOnly = true; // deprecate id field edit to prevent exceptions
dataGridView1->DataSource = dtMain;
}
private: System::Void Form1_FormClosed(System::Object^ sender, System::Windows::Forms::FormClosedEventArgs^ e) {
if (adapter == nullptr) return;
adapter->Update(dtMain);
}
// show tooltip (not intrusive MessageBox) when user trying to input letters into INT column cell
private: System::Void dataGridView1_DataError(System::Object^ sender, System::Windows::Forms::DataGridViewDataErrorEventArgs^ e) {
if (dtMain->Columns[e->ColumnIndex]->DataType == Int64::typeid ||
dtMain->Columns[e->ColumnIndex]->DataType == Int32::typeid ||
dtMain->Columns[e->ColumnIndex]->DataType == Int16::typeid)
{
Rectangle ^rectColumn;
rectColumn = dataGridView1->GetColumnDisplayRectangle(e->ColumnIndex, false);
Rectangle ^rectRow;
rectRow = dataGridView1->GetRowDisplayRectangle(e->RowIndex, false);
toolTip1->ToolTipTitle = "This field is for numbers only.";
toolTip1->Show(" ",
dataGridView1,
rectColumn->Left, rectRow->Top + rectRow->Height);
delete rectColumn;
delete rectRow;
}
}
private: System::Void dataGridView1_MouseDown(System::Object^ sender, System::Windows::Forms::MouseEventArgs^ e) {
toolTip1->Hide(dataGridView1);
}
};
}
SQL Server 2008 R2
Important:
- check if SQL Server services enabled, on the control panel's component Services.
- Redistribution: Microsoft SQL Server 2008 Express: http://www.microsoft.com/ru-ru/download/details.aspx?id=1695
Code in C#:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace SQL_Server_2008_R2_in_CSharp
{
public partial class Form1 : Form
{
string ServerName, DBName;
SqlConnection conn;
SqlDataAdapter adapter;
DataTable dtMain;
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
ServerName = @"localhost\sqlexpress";
DBName = "test";
// connect to server
// to database "master" to check if our database exists
// to create it if it isn't exists
conn = new SqlConnection("Server=" + ServerName + ";Trusted_Connection=Yes;Database=master;");
conn.Open();
// create database if not exists
try
{
using (SqlCommand cmd = new SqlCommand(String.Format("CREATE DATABASE [{0}] ON (" +
" NAME = {0}, " +
" FILENAME = '" + Application.StartupPath + "\\{0}.mdf'" +
");",
DBName), conn))
{
cmd.ExecuteNonQuery();
}
}
catch { }
// connect to db
conn = new SqlConnection("Database=" + DBName + ";Server=" + ServerName + ";Trusted_Connection=Yes;");
conn.Open();
// create table "Table 1" if not exists
using (SqlCommand cmd = new SqlCommand(String.Format(
"IF NOT EXISTS (" +
" SELECT [name] " +
" FROM sys.tables " +
" WHERE [name] = '{0}'" +
") " +
"CREATE TABLE [{0}] (" +
" id [INT] IDENTITY(1,1) PRIMARY KEY CLUSTERED, " +
" [text column] [TEXT] NULL, " +
" [int column] [INT] NULL " +
")",
"Table 1"), conn))
{
cmd.ExecuteNonQuery();
}
// get all tables from DB
using (DataTable dt = conn.GetSchema("Tables"))
{
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i].ItemArray[dt.Columns.IndexOf("TABLE_TYPE")].ToString() == "BASE TABLE")
{
comboBoxTables.Items.Add(dt.Rows[i].ItemArray[dt.Columns.IndexOf("TABLE_NAME")].ToString());
}
}
}
}
private void buttonOK_Click(object sender, EventArgs e)
{
if (comboBoxTables.SelectedItem == null) return;
adapter = new SqlDataAdapter("SELECT * FROM [" + comboBoxTables.SelectedItem.ToString() + "]", conn);
new SqlCommandBuilder(adapter);
dtMain = new DataTable();
adapter.Fill(dtMain);
dtMain.Columns["id"].ReadOnly = true; // deprecate id field edit to prevent exceptions
dataGridView1.DataSource = dtMain;
}
private void Form1_FormClosed(object sender, FormClosedEventArgs e)
{
if (adapter == null) return;
adapter.Update(dtMain);
}
// show tooltip (not intrusive MessageBox) when user trying to input letters into INT column cell
private void dataGridView1_DataError(object sender, DataGridViewDataErrorEventArgs e)
{
if (dtMain.Columns[e.ColumnIndex].DataType == typeof(Int64) ||
dtMain.Columns[e.ColumnIndex].DataType == typeof(Int32) ||
dtMain.Columns[e.ColumnIndex].DataType == typeof(Int16))
{
Rectangle rectColumn;
rectColumn = dataGridView1.GetColumnDisplayRectangle(e.ColumnIndex, false);
Rectangle rectRow;
rectRow = dataGridView1.GetRowDisplayRectangle(e.RowIndex, false);
toolTip1.ToolTipTitle = "This field is for numbers only.";
toolTip1.Show(" ",
dataGridView1,
rectColumn.Left, rectRow.Top + rectRow.Height);
}
}
private void dataGridView1_MouseDown(object sender, MouseEventArgs e)
{
toolTip1.Hide(dataGridView1);
}
}
}
Code in VB.NET:
'=======================================================
'Service provided by Telerik (www.telerik.com)
'Conversion powered by NRefactory.
'Twitter: @telerik
'Facebook: facebook.com/telerik
'=======================================================
Imports System.Data.SqlClient ' SqlConnection, SqlDataAdapter, SqlCommandBuilder
Public Class Form1
Private DBName, ServerName As String
Private conn As SqlConnection
Private adapter As SqlDataAdapter
Private dtMain As DataTable
Public Sub New()
InitializeComponent()
End Sub
Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs)
ServerName = "localhost\sqlexpress"
DBName = "test"
' connect to server
' to database "master" to check if our database exists
' to create it if it isn't exists
conn = New SqlConnection("Server=" + ServerName + ";Trusted_Connection=Yes;Database=master;")
conn.Open()
' create database if not exists
Try
Using cmd As New SqlCommand([String].Format("CREATE DATABASE [{0}] ON (" + " NAME = {0}, " + " FILENAME = '" + Application.StartupPath + "\{0}.mdf'" + ");", DBName), conn)
cmd.ExecuteNonQuery()
End Using
Catch
End Try
' connect to db
conn = New SqlConnection("Database=" + DBName + ";Server=" + ServerName + ";Trusted_Connection=Yes;")
conn.Open()
' create table "Table 1" if not exists
Using cmd As New SqlCommand([String].Format("IF NOT EXISTS (" + " SELECT [name] " + " FROM sys.tables " + " WHERE [name] = '{0}'" + ") " + "CREATE TABLE [{0}] (" + " id [INT] IDENTITY(1,1) PRIMARY KEY CLUSTERED, " + " [text column] [TEXT] NULL, " + " [int column] [INT] NULL " + ")", "Table 1"), conn)
cmd.ExecuteNonQuery()
End Using
' get all tables from DB
Using dt As DataTable = conn.GetSchema("Tables")
For i As Integer = 0 To dt.Rows.Count - 1
If dt.Rows(i).ItemArray(dt.Columns.IndexOf("TABLE_TYPE")).ToString() = "BASE TABLE" Then
ComboBoxTables.Items.Add(dt.Rows(i).ItemArray(dt.Columns.IndexOf("TABLE_NAME")).ToString())
End If
Next
End Using
'=======================================================
'Service provided by Telerik (www.telerik.com)
'Conversion powered by NRefactory.
'Twitter: @telerik
'Facebook: facebook.com/telerik
'=======================================================
End Sub
Private Sub buttonOK_Click(ByVal sender As Object, ByVal e As EventArgs)
If ComboBoxTables.SelectedItem Is Nothing Then
Return
End If
adapter = New SqlDataAdapter("SELECT * FROM [" + ComboBoxTables.SelectedItem.ToString() + "]", conn)
Dim builder = New SqlCommandBuilder(adapter)
dtMain = New DataTable()
adapter.Fill(dtMain)
dtMain.Columns("id").[ReadOnly] = True
' deprecate id field edit to prevent exceptions
DataGridView1.DataSource = dtMain
'=======================================================
'Service provided by Telerik (www.telerik.com)
'Conversion powered by NRefactory.
'Twitter: @telerik
'Facebook: facebook.com/telerik
'=======================================================
End Sub
Private Sub Form1_FormClosed(ByVal sender As Object, ByVal e As FormClosedEventArgs)
If adapter Is Nothing Then
Return
End If
adapter.Update(dtMain)
End Sub
' show tooltip (not intrusive MessageBox) when user trying to input letters into INT column cell
Private Sub dataGridView1_DataError(ByVal sender As Object, ByVal e As DataGridViewDataErrorEventArgs)
If dtMain.Columns(e.ColumnIndex).DataType Is GetType(Int64) Or _
dtMain.Columns(e.ColumnIndex).DataType Is GetType(Int32) Or _
dtMain.Columns(e.ColumnIndex).DataType Is GetType(Int16) Then
Dim rectColumn As Rectangle
rectColumn = DataGridView1.GetColumnDisplayRectangle(e.ColumnIndex, False)
Dim rectRow As Rectangle
rectRow = DataGridView1.GetRowDisplayRectangle(e.RowIndex, False)
ToolTip1.ToolTipTitle = "This field is for numbers only."
ToolTip1.Show(" ", DataGridView1, rectColumn.Left, rectRow.Top + rectRow.Height)
End If
End Sub
Private Sub dataGridView1_MouseDown(ByVal sender As Object, ByVal e As MouseEventArgs)
toolTip1.Hide(dataGridView1)
End Sub
'=======================================================
'Service provided by Telerik (www.telerik.com)
'Conversion powered by NRefactory.
'Twitter: @telerik
'Facebook: facebook.com/telerik
'=======================================================
End Class
'=======================================================
'Service provided by Telerik (www.telerik.com)
'Conversion powered by NRefactory.
'Twitter: @telerik
'Facebook: facebook.com/telerik
'=======================================================
Code in C++/CLI:
#pragma once
namespace SQLServer2008R2inCCLI {
using namespace System;
using namespace System::ComponentModel;
using namespace System::Collections;
using namespace System::Windows::Forms;
using namespace System::Data;
using namespace System::Drawing;
using namespace System::Data::SqlClient;
/// <summary>
/// Summary for Form1
///
/// WARNING: If you change the name of this class, you will need to change the
/// 'Resource File Name' property for the managed resource compiler tool
/// associated with all .resx files this class depends on. Otherwise,
/// the designers will not be able to interact properly with localized
/// resources associated with this form.
/// </summary>
public ref class Form1 : public System::Windows::Forms::Form
{
public:
Form1(void)
{
InitializeComponent();
//
//TODO: Add the constructor code here
//
}
protected:
/// <summary>
/// Clean up any resources being used.
/// </summary>
~Form1()
{
if (components)
{
delete components;
}
}
private: System::Windows::Forms::DataGridView^ dataGridView1;
private: System::Windows::Forms::Label^ label1;
private: System::Windows::Forms::ComboBox^ comboBoxTables;
private: System::Windows::Forms::Button^ buttonOK;
private: System::Windows::Forms::ToolTip^ toolTip1;
private: System::ComponentModel::IContainer^ components;
protected:
private:
/// <summary>
/// Required designer variable.
/// </summary>
#pragma region Windows Form Designer generated code
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
void InitializeComponent(void)
{
this->components = (gcnew System::ComponentModel::Container());
this->dataGridView1 = (gcnew System::Windows::Forms::DataGridView());
this->label1 = (gcnew System::Windows::Forms::Label());
this->comboBoxTables = (gcnew System::Windows::Forms::ComboBox());
this->buttonOK = (gcnew System::Windows::Forms::Button());
this->toolTip1 = (gcnew System::Windows::Forms::ToolTip(this->components));
(cli::safe_cast<System::ComponentModel::ISupportInitialize^ >(this->dataGridView1))->BeginInit();
this->SuspendLayout();
//
// dataGridView1
//
this->dataGridView1->Anchor = static_cast<System::Windows::Forms::AnchorStyles>((((System::Windows::Forms::AnchorStyles::Top | System::Windows::Forms::AnchorStyles::Bottom)
| System::Windows::Forms::AnchorStyles::Left)
| System::Windows::Forms::AnchorStyles::Right));
this->dataGridView1->ColumnHeadersHeightSizeMode = System::Windows::Forms::DataGridViewColumnHeadersHeightSizeMode::AutoSize;
this->dataGridView1->Location = System::Drawing::Point(12, 44);
this->dataGridView1->Name = L"dataGridView1";
this->dataGridView1->ShowCellToolTips = false;
this->dataGridView1->Size = System::Drawing::Size(493, 266);
this->dataGridView1->TabIndex = 0;
this->dataGridView1->MouseDown += gcnew System::Windows::Forms::MouseEventHandler(this, &Form1::dataGridView1_MouseDown);
this->dataGridView1->DataError += gcnew System::Windows::Forms::DataGridViewDataErrorEventHandler(this, &Form1::dataGridView1_DataError);
//
// label1
//
this->label1->Location = System::Drawing::Point(12, 9);
this->label1->Name = L"label1";
this->label1->Size = System::Drawing::Size(65, 32);
this->label1->TabIndex = 1;
this->label1->Text = L"Select table to show:";
//
// comboBoxTables
//
this->comboBoxTables->DropDownStyle = System::Windows::Forms::ComboBoxStyle::DropDownList;
this->comboBoxTables->FormattingEnabled = true;
this->comboBoxTables->Location = System::Drawing::Point(83, 12);
this->comboBoxTables->Name = L"comboBoxTables";
this->comboBoxTables->Size = System::Drawing::Size(165, 21);
this->comboBoxTables->TabIndex = 2;
//
// buttonOK
//
this->buttonOK->Location = System::Drawing::Point(254, 12);
this->buttonOK->Name = L"buttonOK";
this->buttonOK->Size = System::Drawing::Size(56, 21);
this->buttonOK->TabIndex = 3;
this->buttonOK->Text = L"OK";
this->buttonOK->UseVisualStyleBackColor = true;
this->buttonOK->Click += gcnew System::EventHandler(this, &Form1::buttonOK_Click);
//
// toolTip1
//
this->toolTip1->AutoPopDelay = 5000;
this->toolTip1->InitialDelay = 0;
this->toolTip1->IsBalloon = true;
this->toolTip1->ReshowDelay = 0;
this->toolTip1->ToolTipIcon = System::Windows::Forms::ToolTipIcon::Warning;
this->toolTip1->UseAnimation = false;
this->toolTip1->UseFading = false;
//
// Form1
//
this->AutoScaleDimensions = System::Drawing::SizeF(6, 13);
this->AutoScaleMode = System::Windows::Forms::AutoScaleMode::Font;
this->ClientSize = System::Drawing::Size(517, 322);
this->Controls->Add(this->buttonOK);
this->Controls->Add(this->comboBoxTables);
this->Controls->Add(this->label1);
this->Controls->Add(this->dataGridView1);
this->Name = L"Form1";
this->Text = L"Form1";
this->Load += gcnew System::EventHandler(this, &Form1::Form1_Load);
this->FormClosed += gcnew System::Windows::Forms::FormClosedEventHandler(this, &Form1::Form1_FormClosed);
(cli::safe_cast<System::ComponentModel::ISupportInitialize^ >(this->dataGridView1))->EndInit();
this->ResumeLayout(false);
}
#pragma endregion
String ^ServerName, ^DBName;
SqlConnection ^conn;
SqlDataAdapter ^adapter;
DataTable ^dtMain;
private: System::Void Form1_Load(System::Object^ sender, System::EventArgs^ e) {
ServerName = "localhost\\sqlexpress";
DBName = "test";
// connect to server
// to database "master" to check if our database exists
// to create it if it isn't exists
conn = gcnew SqlConnection("Server=" + ServerName + ";Trusted_Connection=Yes;Database=master;");
conn->Open();
// create database if not exists
try {
SqlCommand ^cmd = gcnew SqlCommand(String::Format("CREATE DATABASE [{0}] ON (" +
" NAME = {0}, " +
" FILENAME = '" + Application::StartupPath + "\\{0}.mdf'" +
");",
DBName), conn);
cmd->ExecuteNonQuery();
delete cmd;
}
catch (Exception ^ex) { }
// connect to db
conn = gcnew SqlConnection("Database=" + DBName + ";Server=" + ServerName + ";Trusted_Connection=Yes;");
conn->Open();
// create table "Table 1" if not exists
SqlCommand ^cmd = gcnew SqlCommand(String::Format(
"IF NOT EXISTS (" +
" SELECT [name] " +
" FROM sys.tables " +
" WHERE [name] = '{0}'" +
") " +
"CREATE TABLE [{0}] (" +
" id [INT] IDENTITY(1,1) PRIMARY KEY CLUSTERED, " +
" [text column] [TEXT] NULL, " +
" [int column] [INT] NULL " +
")",
"Table 1"), conn);
cmd->ExecuteNonQuery();
delete cmd;
// get all tables from DB
DataTable ^dt = conn->GetSchema("Tables");
for (int i = 0; i < dt->Rows->Count; i++) {
if (dt->Rows[i]->ItemArray[dt->Columns->IndexOf("TABLE_TYPE")]->ToString() == "BASE TABLE") {
comboBoxTables->Items->Add(dt->Rows[i]->ItemArray[dt->Columns->IndexOf("TABLE_NAME")]->ToString());
}
}
delete dt;
}
private: System::Void buttonOK_Click(System::Object^ sender, System::EventArgs^ e) {
if (comboBoxTables->SelectedItem == nullptr) return;
adapter = gcnew SqlDataAdapter("SELECT * FROM [" + comboBoxTables->SelectedItem->ToString() + "]", conn);
gcnew SqlCommandBuilder(adapter);
dtMain = gcnew DataTable();
adapter->Fill(dtMain);
dtMain->Columns["id"]->ReadOnly = true; // deprecate id field edit to prevent exceptions
dataGridView1->DataSource = dtMain;
}
private: System::Void Form1_FormClosed(System::Object^ sender, System::Windows::Forms::FormClosedEventArgs^ e) {
if (adapter == nullptr) return;
adapter->Update(dtMain);
}
// show tooltip (not intrusive MessageBox) when user trying to input letters into INT column cell
private: System::Void dataGridView1_DataError(System::Object^ sender, System::Windows::Forms::DataGridViewDataErrorEventArgs^ e) {
if (dtMain->Columns[e->ColumnIndex]->DataType == Int64::typeid ||
dtMain->Columns[e->ColumnIndex]->DataType == Int32::typeid ||
dtMain->Columns[e->ColumnIndex]->DataType == Int16::typeid)
{
Rectangle ^rectColumn;
rectColumn = dataGridView1->GetColumnDisplayRectangle(e->ColumnIndex, false);
Rectangle ^rectRow;
rectRow = dataGridView1->GetRowDisplayRectangle(e->RowIndex, false);
toolTip1->ToolTipTitle = "This field is for numbers only.";
toolTip1->Show(" ",
dataGridView1,
rectColumn->Left, rectRow->Top + rectRow->Height);
delete rectColumn;
delete rectRow;
}
}
private: System::Void dataGridView1_MouseDown(System::Object^ sender, System::Windows::Forms::MouseEventArgs^ e) {
toolTip1->Hide(dataGridView1);
}
};
}
SQL Server Compact 3.5
Important:
- add a reference to System.Data.SqlServerCe (tested on its 3.5 version).
Code in C#:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Data.SqlServerCe;
namespace SQL_Server_CE_in_CSharp
{
public partial class Form1 : Form
{
string DBPath;
SqlCeConnection conn;
SqlCeDataAdapter adapter;
DataTable dtMain;
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
DBPath = Application.StartupPath + "\\test.sdf";
// create db if not exists
if (!File.Exists(DBPath))
{
using (SqlCeEngine se = new SqlCeEngine("Data Source=" + DBPath))
{
se.CreateDatabase();
}
}
// connect to db
conn = new SqlCeConnection("Data Source=" + DBPath);
conn.Open();
// create table "Table 1" if not exists
try
{
using (SqlCeCommand cmd = new SqlCeCommand("CREATE TABLE [Table 1] (id INTEGER IDENTITY(1,1) PRIMARY KEY, [text column] NTEXT, [int column] INT);", conn))
{
cmd.ExecuteNonQuery();
}
}
catch { }
// get all tables from DB
using (DataTable dt = new DataTable())
{
using (SqlCeDataAdapter ad = new SqlCeDataAdapter("SELECT table_name FROM information_schema.tables WHERE TABLE_TYPE <> 'VIEW'", conn))
{
ad.Fill(dt);
for (int i = 0; i < dt.Rows.Count; i++)
{
comboBoxTables.Items.Add(dt.Rows[i].ItemArray[dt.Columns.IndexOf("TABLE_NAME")].ToString());
}
}
}
}
private void buttonOK_Click(object sender, EventArgs e)
{
if (comboBoxTables.SelectedItem == null) return;
adapter = new SqlCeDataAdapter("SELECT * FROM [" + comboBoxTables.SelectedItem.ToString() + "]", conn);
new SqlCeCommandBuilder(adapter);
dtMain = new DataTable();
adapter.Fill(dtMain);
dtMain.Columns["id"].ReadOnly = true; // deprecate id field edit to prevent exceptions
dataGridView1.DataSource = dtMain;
}
private void Form1_FormClosed(object sender, FormClosedEventArgs e)
{
if (adapter == null) return;
adapter.Update(dtMain);
}
// show tooltip (not intrusive MessageBox) when user trying to input letters into INT column cell
private void dataGridView1_DataError(object sender, DataGridViewDataErrorEventArgs e)
{
if (dtMain.Columns[e.ColumnIndex].DataType == typeof(Int64) ||
dtMain.Columns[e.ColumnIndex].DataType == typeof(Int32) ||
dtMain.Columns[e.ColumnIndex].DataType == typeof(Int16))
{
Rectangle rectColumn;
rectColumn = dataGridView1.GetColumnDisplayRectangle(e.ColumnIndex, false);
Rectangle rectRow;
rectRow = dataGridView1.GetRowDisplayRectangle(e.RowIndex, false);
toolTip1.ToolTipTitle = "This field is for numbers only.";
toolTip1.Show(" ",
dataGridView1,
rectColumn.Left, rectRow.Top + rectRow.Height);
}
}
private void dataGridView1_MouseDown(object sender, MouseEventArgs e)
{
toolTip1.Hide(dataGridView1);
}
}
}
Code in VB.NET:
'=======================================================
'Service provided by Telerik (www.telerik.com)
'Conversion powered by NRefactory.
'Twitter: @telerik
'Facebook: facebook.com/telerik
'=======================================================
Imports System.IO 'File.Exists()
Imports System.Data.SqlServerCe ' SqlCeConnection, SqlCeDataAdapter, SqlCeCommandBuilder
' WARNING: Add a reference to System.Data.SqlServerCe!
Public Class Form1
Dim DBPath As String
Dim conn As SqlCeConnection
Dim adapter As SqlCeDataAdapter
Dim dtMain As DataTable
'=======================================================
'Service provided by Telerik (www.telerik.com)
'Conversion powered by NRefactory.
'Twitter: @telerik
'Facebook: facebook.com/telerik
'=======================================================
Public Sub New()
InitializeComponent()
End Sub
Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs)
DBPath = Application.StartupPath + "\test.sdf"
' create db if not exists
If Not File.Exists(DBPath) Then
Using se As New SqlCeEngine("Data Source=" + DBPath)
se.CreateDatabase()
End Using
End If
' connect to db
conn = New SqlCeConnection("Data Source=" + DBPath)
conn.Open()
' create table "Table 1" if not exists
Try
Using cmd As New SqlCeCommand("CREATE TABLE [Table 1] (id INTEGER IDENTITY(1,1) PRIMARY KEY, [text column] NTEXT, [int column] INT);", conn)
cmd.ExecuteNonQuery()
End Using
Catch
End Try
' get all tables from DB
Using dt As New DataTable()
Using ad As New SqlCeDataAdapter("SELECT table_name FROM information_schema.tables WHERE TABLE_TYPE <> 'VIEW'", conn)
ad.Fill(dt)
For i As Integer = 0 To dt.Rows.Count - 1
ComboBoxTables.Items.Add(dt.Rows(i).ItemArray(dt.Columns.IndexOf("TABLE_NAME")).ToString())
Next
End Using
End Using
'=======================================================
'Service provided by Telerik (www.telerik.com)
'Conversion powered by NRefactory.
'Twitter: @telerik
'Facebook: facebook.com/telerik
'=======================================================
End Sub
Private Sub buttonOK_Click(ByVal sender As Object, ByVal e As EventArgs)
If ComboBoxTables.SelectedItem Is Nothing Then
Return
End If
adapter = New SqlCeDataAdapter("SELECT * FROM [" + ComboBoxTables.SelectedItem.ToString() + "]", conn)
Dim builder = New SqlCeCommandBuilder(adapter)
dtMain = New DataTable()
adapter.Fill(dtMain)
dtMain.Columns("id").[ReadOnly] = True
' deprecate id field edit to prevent exceptions
DataGridView1.DataSource = dtMain
'=======================================================
'Service provided by Telerik (www.telerik.com)
'Conversion powered by NRefactory.
'Twitter: @telerik
'Facebook: facebook.com/telerik
'=======================================================
End Sub
Private Sub Form1_FormClosed(ByVal sender As Object, ByVal e As FormClosedEventArgs)
If adapter Is Nothing Then
Return
End If
adapter.Update(dtMain)
End Sub
' show tooltip (not intrusive MessageBox) when user trying to input letters into INT column cell
Private Sub dataGridView1_DataError(ByVal sender As Object, ByVal e As DataGridViewDataErrorEventArgs)
If dtMain.Columns(e.ColumnIndex).DataType Is GetType(Int64) Or _
dtMain.Columns(e.ColumnIndex).DataType Is GetType(Int32) Or _
dtMain.Columns(e.ColumnIndex).DataType Is GetType(Int16) Then
Dim rectColumn As Rectangle
rectColumn = DataGridView1.GetColumnDisplayRectangle(e.ColumnIndex, False)
Dim rectRow As Rectangle
rectRow = DataGridView1.GetRowDisplayRectangle(e.RowIndex, False)
ToolTip1.ToolTipTitle = "This field is for numbers only."
ToolTip1.Show(" ", DataGridView1, rectColumn.Left, rectRow.Top + rectRow.Height)
End If
End Sub
Private Sub dataGridView1_MouseDown(ByVal sender As Object, ByVal e As MouseEventArgs)
toolTip1.Hide(dataGridView1)
End Sub
'=======================================================
'Service provided by Telerik (www.telerik.com)
'Conversion powered by NRefactory.
'Twitter: @telerik
'Facebook: facebook.com/telerik
'=======================================================
End Class
'=======================================================
'Service provided by Telerik (www.telerik.com)
'Conversion powered by NRefactory.
'Twitter: @telerik
'Facebook: facebook.com/telerik
'=======================================================
Code in C++/CLI:
#pragma once
namespace SQLServerCEinCCLI {
using namespace System;
using namespace System::ComponentModel;
using namespace System::Collections;
using namespace System::Windows::Forms;
using namespace System::Data;
using namespace System::Drawing;
using namespace System::IO; // File::Exists()
using namespace System::Data::SqlServerCe;
/// <summary>
/// Summary for Form1
///
/// WARNING: If you change the name of this class, you will need to change the
/// 'Resource File Name' property for the managed resource compiler tool
/// associated with all .resx files this class depends on. Otherwise,
/// the designers will not be able to interact properly with localized
/// resources associated with this form.
/// </summary>
public ref class Form1 : public System::Windows::Forms::Form
{
public:
Form1(void)
{
InitializeComponent();
//
//TODO: Add the constructor code here
//
}
protected:
/// <summary>
/// Clean up any resources being used.
/// </summary>
~Form1()
{
if (components)
{
delete components;
}
}
private: System::Windows::Forms::DataGridView^ dataGridView1;
private: System::Windows::Forms::Label^ label1;
private: System::Windows::Forms::ComboBox^ comboBoxTables;
private: System::Windows::Forms::Button^ buttonOK;
private: System::Windows::Forms::ToolTip^ toolTip1;
private: System::ComponentModel::IContainer^ components;
protected:
private:
/// <summary>
/// Required designer variable.
/// </summary>
#pragma region Windows Form Designer generated code
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
void InitializeComponent(void)
{
this->components = (gcnew System::ComponentModel::Container());
this->dataGridView1 = (gcnew System::Windows::Forms::DataGridView());
this->label1 = (gcnew System::Windows::Forms::Label());
this->comboBoxTables = (gcnew System::Windows::Forms::ComboBox());
this->buttonOK = (gcnew System::Windows::Forms::Button());
this->toolTip1 = (gcnew System::Windows::Forms::ToolTip(this->components));
(cli::safe_cast<System::ComponentModel::ISupportInitialize^ >(this->dataGridView1))->BeginInit();
this->SuspendLayout();
//
// dataGridView1
//
this->dataGridView1->Anchor = static_cast<System::Windows::Forms::AnchorStyles>((((System::Windows::Forms::AnchorStyles::Top | System::Windows::Forms::AnchorStyles::Bottom)
| System::Windows::Forms::AnchorStyles::Left)
| System::Windows::Forms::AnchorStyles::Right));
this->dataGridView1->ColumnHeadersHeightSizeMode = System::Windows::Forms::DataGridViewColumnHeadersHeightSizeMode::AutoSize;
this->dataGridView1->Location = System::Drawing::Point(12, 44);
this->dataGridView1->Name = L"dataGridView1";
this->dataGridView1->ShowCellToolTips = false;
this->dataGridView1->Size = System::Drawing::Size(493, 266);
this->dataGridView1->TabIndex = 0;
this->dataGridView1->MouseDown += gcnew System::Windows::Forms::MouseEventHandler(this, &Form1::dataGridView1_MouseDown);
this->dataGridView1->DataError += gcnew System::Windows::Forms::DataGridViewDataErrorEventHandler(this, &Form1::dataGridView1_DataError);
//
// label1
//
this->label1->Location = System::Drawing::Point(12, 9);
this->label1->Name = L"label1";
this->label1->Size = System::Drawing::Size(65, 32);
this->label1->TabIndex = 1;
this->label1->Text = L"Select table to show:";
//
// comboBoxTables
//
this->comboBoxTables->DropDownStyle = System::Windows::Forms::ComboBoxStyle::DropDownList;
this->comboBoxTables->FormattingEnabled = true;
this->comboBoxTables->Location = System::Drawing::Point(83, 12);
this->comboBoxTables->Name = L"comboBoxTables";
this->comboBoxTables->Size = System::Drawing::Size(165, 21);
this->comboBoxTables->TabIndex = 2;
//
// buttonOK
//
this->buttonOK->Location = System::Drawing::Point(254, 12);
this->buttonOK->Name = L"buttonOK";
this->buttonOK->Size = System::Drawing::Size(56, 21);
this->buttonOK->TabIndex = 3;
this->buttonOK->Text = L"OK";
this->buttonOK->UseVisualStyleBackColor = true;
this->buttonOK->Click += gcnew System::EventHandler(this, &Form1::buttonOK_Click);
//
// toolTip1
//
this->toolTip1->AutoPopDelay = 5000;
this->toolTip1->InitialDelay = 0;
this->toolTip1->IsBalloon = true;
this->toolTip1->ReshowDelay = 0;
this->toolTip1->ToolTipIcon = System::Windows::Forms::ToolTipIcon::Warning;
this->toolTip1->UseAnimation = false;
this->toolTip1->UseFading = false;
//
// Form1
//
this->AutoScaleDimensions = System::Drawing::SizeF(6, 13);
this->AutoScaleMode = System::Windows::Forms::AutoScaleMode::Font;
this->ClientSize = System::Drawing::Size(517, 322);
this->Controls->Add(this->buttonOK);
this->Controls->Add(this->comboBoxTables);
this->Controls->Add(this->label1);
this->Controls->Add(this->dataGridView1);
this->Name = L"Form1";
this->Text = L"Form1";
this->Load += gcnew System::EventHandler(this, &Form1::Form1_Load);
this->FormClosed += gcnew System::Windows::Forms::FormClosedEventHandler(this, &Form1::Form1_FormClosed);
(cli::safe_cast<System::ComponentModel::ISupportInitialize^ >(this->dataGridView1))->EndInit();
this->ResumeLayout(false);
}
#pragma endregion
String ^DBPath;
SqlCeConnection ^conn;
SqlCeDataAdapter ^adapter;
DataTable ^dtMain;
private: System::Void Form1_Load(System::Object^ sender, System::EventArgs^ e) {
DBPath = Application::StartupPath + "\\test.sdf";
// create db if not exists
if (!File::Exists(DBPath)) {
SqlCeEngine ^se = gcnew SqlCeEngine("Data Source=" + DBPath);
se->CreateDatabase();
delete se;
}
// connect to db
conn = gcnew SqlCeConnection("Data Source=" + DBPath);
conn->Open();
// create table "Table 1" if not exists
try {
SqlCeCommand ^cmd = gcnew SqlCeCommand("CREATE TABLE [Table 1] (id INTEGER IDENTITY(1,1) PRIMARY KEY, [text column] NTEXT, [int column] INT);", conn);
cmd->ExecuteNonQuery();
delete cmd;
}
catch (Exception ^ex) {
}
// get all tables from DB
DataTable ^dt = gcnew DataTable();
SqlCeDataAdapter ^ad = gcnew SqlCeDataAdapter("SELECT table_name FROM information_schema.tables WHERE TABLE_TYPE <> 'VIEW'", conn);
ad->Fill(dt);
for (int i = 0; i < dt->Rows->Count; i++) {
comboBoxTables->Items->Add(dt->Rows[i]->ItemArray[dt->Columns->IndexOf("TABLE_NAME")]->ToString());
}
delete ad;
delete dt;
}
private: System::Void buttonOK_Click(System::Object^ sender, System::EventArgs^ e) {
if (comboBoxTables->SelectedItem == nullptr) return;
adapter = gcnew SqlCeDataAdapter("SELECT * FROM [" + comboBoxTables->SelectedItem->ToString() + "]", conn);
gcnew SqlCeCommandBuilder(adapter);
dtMain = gcnew DataTable();
adapter->Fill(dtMain);
dtMain->Columns["id"]->ReadOnly = true; // deprecate id field edit to prevent exceptions
dataGridView1->DataSource = dtMain;
}
private: System::Void Form1_FormClosed(System::Object^ sender, System::Windows::Forms::FormClosedEventArgs^ e) {
if (adapter == nullptr) return;
adapter->Update(dtMain);
}
// show tooltip (not intrusive MessageBox) when user trying to input letters into INT column cell
private: System::Void dataGridView1_DataError(System::Object^ sender, System::Windows::Forms::DataGridViewDataErrorEventArgs^ e) {
if (dtMain->Columns[e->ColumnIndex]->DataType == Int64::typeid ||
dtMain->Columns[e->ColumnIndex]->DataType == Int32::typeid ||
dtMain->Columns[e->ColumnIndex]->DataType == Int16::typeid)
{
Rectangle ^rectColumn;
rectColumn = dataGridView1->GetColumnDisplayRectangle(e->ColumnIndex, false);
Rectangle ^rectRow;
rectRow = dataGridView1->GetRowDisplayRectangle(e->RowIndex, false);
toolTip1->ToolTipTitle = "This field is for numbers only.";
toolTip1->Show(" ",
dataGridView1,
rectColumn->Left, rectRow->Top + rectRow->Height);
delete rectColumn;
delete rectRow;
}
}
private: System::Void dataGridView1_MouseDown(System::Object^ sender, System::Windows::Forms::MouseEventArgs^ e) {
toolTip1->Hide(dataGridView1);
}
};
}