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;
using System.Data.OleDb;
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";
if (!File.Exists(DBPath))
{
ADOX.Catalog cat = new ADOX.Catalog();
cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + DBPath);
cat = null;
}
conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + DBPath);
conn.Open();
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; }
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;
dataGridView1.DataSource = dtMain;
}
private void Form1_FormClosed(object sender, FormClosedEventArgs e)
{
if (adapter == null) return;
adapter.Update(dtMain);
}
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:
Imports System.IO
Imports System.Data.OleDb
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"
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
conn = New OleDbConnection(Convert.ToString("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=") & DBPath)
conn.Open()
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
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
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
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
End Class
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; using namespace System::Data::OleDb;
public ref class Form1 : public System::Windows::Forms::Form
{
public:
Form1(void)
{
InitializeComponent();
}
protected:
~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:
#pragma region Windows Form Designer generated code
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();
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);
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:";
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;
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);
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;
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";
if (!File::Exists(DBPath)) {
ADOX::Catalog ^cat = gcnew ADOX::Catalog();
cat->Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + DBPath);
cat = nullptr;
}
conn = gcnew OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + DBPath);
conn->Open();
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; }
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; dataGridView1->DataSource = dtMain;
}
private: System::Void Form1_FormClosed(System::Object^ sender, System::Windows::Forms::FormClosedEventArgs^ e) {
if (adapter == nullptr) return;
adapter->Update(dtMain);
}
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;
using System.Data.OleDb;
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";
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;
}
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();
}
}
}
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; }
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;
dataGridView1.DataSource = dtMain;
}
private void Form1_FormClosed(object sender, FormClosedEventArgs e)
{
if (adapter == null) return;
adapter.Update(dtMain);
}
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:
Imports System.IO
Imports System.Data.OleDb
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"
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
cat = Nothing
End If
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
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
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
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
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
End Class
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; using namespace System::Data::OleDb;
public ref class Form1 : public System::Windows::Forms::Form
{
public:
Form1(void)
{
InitializeComponent();
}
protected:
~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:
#pragma region Windows Form Designer generated code
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();
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);
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:";
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;
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);
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;
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";
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;
}
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();
}
}
}
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; }
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; dataGridView1->DataSource = dtMain;
}
private: System::Void Form1_FormClosed(System::Object^ sender, System::Windows::Forms::FormClosedEventArgs^ e) {
if (adapter == nullptr) return;
adapter->Update(dtMain);
}
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";
conn = new MySqlConnection("Server=localhost;Uid=root;Pwd=;");
conn.Open();
try
{
using (MySqlCommand cmd = new MySqlCommand("CREATE DATABASE " + DBName, conn))
{
cmd.ExecuteNonQuery();
}
}
catch { }
using (MySqlCommand cmd = new MySqlCommand("USE " + DBName, conn))
{
cmd.ExecuteNonQuery();
}
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();
}
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;
dataGridView1.DataSource = dtMain;
}
private void Form1_FormClosed(object sender, FormClosedEventArgs e)
{
if (adapter == null) return;
adapter.Update(dtMain);
}
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:
Imports MySql.Data.MySqlClient
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"
conn = New MySqlConnection("Server=localhost;Uid=root;Pwd=;")
conn.Open()
Try
Using cmd As New MySqlCommand(Convert.ToString("CREATE DATABASE ") & DBName, conn)
cmd.ExecuteNonQuery()
End Using
Catch
End Try
Using cmd As New MySqlCommand(Convert.ToString("USE ") & DBName, conn)
cmd.ExecuteNonQuery()
End Using
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
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
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
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
End Class
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;
public ref class Form1 : public System::Windows::Forms::Form
{
public:
Form1(void)
{
InitializeComponent();
}
protected:
~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:
#pragma region Windows Form Designer generated code
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();
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);
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:";
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;
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);
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;
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";
conn = gcnew MySqlConnection("Server=localhost;Uid=root;Pwd=;");
conn->Open();
try {
MySqlCommand ^cmd = gcnew MySqlCommand("CREATE DATABASE " + DBName, conn);
cmd->ExecuteNonQuery();
delete cmd;
}
catch (Exception ^ex) { }
MySqlCommand ^cmd = gcnew MySqlCommand("USE " + DBName, conn);
cmd->ExecuteNonQuery();
delete cmd;
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;
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; dataGridView1->DataSource = dtMain;
}
private: System::Void Form1_FormClosed(System::Object^ sender, System::Windows::Forms::FormClosedEventArgs^ e) {
if (adapter == nullptr) return;
adapter->Update(dtMain);
}
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";
conn = new SQLiteConnection("Data Source=" + DBPath);
conn.Open();
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();
}
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;
dataGridView1.DataSource = dtMain;
}
private void Form1_FormClosed(object sender, FormClosedEventArgs e)
{
if (adapter == null) return;
adapter.Update(dtMain);
}
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:
Imports System.Data.SQLite
Public Class Form1
Dim DBPath As String
Dim conn As SQLiteConnection
Dim adapter As SQLiteDataAdapter
Dim 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.db"
conn = New SQLiteConnection("Data Source=" + DBPath)
conn.Open()
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
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 SQLiteDataAdapter("SELECT * FROM [" + ComboBoxTables.SelectedItem.ToString() + "]", conn)
Dim builder = New SQLiteCommandBuilder(adapter)
dtMain = New DataTable()
adapter.Fill(dtMain)
dtMain.Columns("id").[ReadOnly] = True
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
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
End Class
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;
public ref class Form1 : public System::Windows::Forms::Form
{
public:
Form1(void)
{
InitializeComponent();
}
protected:
~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:
#pragma region Windows Form Designer generated code
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();
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);
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:";
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;
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);
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;
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";
conn = gcnew SQLiteConnection("Data Source=" + DBPath);
conn->Open();
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;
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; dataGridView1->DataSource = dtMain;
}
private: System::Void Form1_FormClosed(System::Object^ sender, System::Windows::Forms::FormClosedEventArgs^ e) {
if (adapter == nullptr) return;
adapter->Update(dtMain);
}
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:
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";
conn = new SqlConnection("Server=" + ServerName + ";Trusted_Connection=Yes;Database=master;");
conn.Open();
try
{
using (SqlCommand cmd = new SqlCommand(String.Format("CREATE DATABASE [{0}] ON (" +
" NAME = {0}, " +
" FILENAME = '" + Application.StartupPath + "\\{0}.mdf'" +
");",
DBName), conn))
{
cmd.ExecuteNonQuery();
}
}
catch { }
conn = new SqlConnection("Database=" + DBName + ";Server=" + ServerName + ";Trusted_Connection=Yes;");
conn.Open();
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();
}
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;
dataGridView1.DataSource = dtMain;
}
private void Form1_FormClosed(object sender, FormClosedEventArgs e)
{
if (adapter == null) return;
adapter.Update(dtMain);
}
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:
Imports System.Data.SqlClient
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"
conn = New SqlConnection("Server=" + ServerName + ";Trusted_Connection=Yes;Database=master;")
conn.Open()
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
conn = New SqlConnection("Database=" + DBName + ";Server=" + ServerName + ";Trusted_Connection=Yes;")
conn.Open()
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
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
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
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
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
End Class
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;
public ref class Form1 : public System::Windows::Forms::Form
{
public:
Form1(void)
{
InitializeComponent();
}
protected:
~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:
#pragma region Windows Form Designer generated code
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();
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);
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:";
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;
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);
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;
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";
conn = gcnew SqlConnection("Server=" + ServerName + ";Trusted_Connection=Yes;Database=master;");
conn->Open();
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) { }
conn = gcnew SqlConnection("Database=" + DBName + ";Server=" + ServerName + ";Trusted_Connection=Yes;");
conn->Open();
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;
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; dataGridView1->DataSource = dtMain;
}
private: System::Void Form1_FormClosed(System::Object^ sender, System::Windows::Forms::FormClosedEventArgs^ e) {
if (adapter == nullptr) return;
adapter->Update(dtMain);
}
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";
if (!File.Exists(DBPath))
{
using (SqlCeEngine se = new SqlCeEngine("Data Source=" + DBPath))
{
se.CreateDatabase();
}
}
conn = new SqlCeConnection("Data Source=" + DBPath);
conn.Open();
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 { }
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;
dataGridView1.DataSource = dtMain;
}
private void Form1_FormClosed(object sender, FormClosedEventArgs e)
{
if (adapter == null) return;
adapter.Update(dtMain);
}
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:
Imports System.IO
Imports System.Data.SqlServerCe
Public Class Form1
Dim DBPath As String
Dim conn As SqlCeConnection
Dim adapter As SqlCeDataAdapter
Dim 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.sdf"
If Not File.Exists(DBPath) Then
Using se As New SqlCeEngine("Data Source=" + DBPath)
se.CreateDatabase()
End Using
End If
conn = New SqlCeConnection("Data Source=" + DBPath)
conn.Open()
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
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
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
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
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
End Class
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; using namespace System::Data::SqlServerCe;
public ref class Form1 : public System::Windows::Forms::Form
{
public:
Form1(void)
{
InitializeComponent();
}
protected:
~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:
#pragma region Windows Form Designer generated code
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();
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);
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:";
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;
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);
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;
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";
if (!File::Exists(DBPath)) {
SqlCeEngine ^se = gcnew SqlCeEngine("Data Source=" + DBPath);
se->CreateDatabase();
delete se;
}
conn = gcnew SqlCeConnection("Data Source=" + DBPath);
conn->Open();
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) {
}
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; dataGridView1->DataSource = dtMain;
}
private: System::Void Form1_FormClosed(System::Object^ sender, System::Windows::Forms::FormClosedEventArgs^ e) {
if (adapter == nullptr) return;
adapter->Update(dtMain);
}
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);
}
};
}
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
(