2. Add Local Database - Database.sdf
3. Add new Table
4. Add static class
SQLFunctions static class:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlServerCe;
using System.Data;
using System.Windows.Forms;
namespace LocalDatabaseTutorial
{
static class SQLFunctions
{
static private SqlCeConnection connection = new SqlCeConnection(@"Data Source=|DataDirectory|\Database.sdf");
static public void Refresh(DataGridView dgv)
{
try
{
connection.Open();
SqlCeDataAdapter dataAdapter = new SqlCeDataAdapter("select * from [Table]", connection);
DataTable dataTable = new DataTable();
dataAdapter.Fill(dataTable);
dgv.DataSource = dataTable;
}
catch (SqlCeException ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
connection.Close();
}
}
static public void Insert(string username)
{
try
{
connection.Open();
SqlCeCommand commandInsert = new SqlCeCommand("insert into[Table] values(@username)", connection);
commandInsert.Parameters.Add("@username", username);
commandInsert.ExecuteNonQuery();
}
catch (SqlCeException ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
connection.Close();
}
}
static public void Delete(string username)
{
try
{
connection.Open();
SqlCeCommand commandInsert = new SqlCeCommand("delete from [Table] where username = @username", connection);
commandInsert.Parameters.Add("@username", username);
commandInsert.ExecuteNonQuery();
}
catch (SqlCeException ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
connection.Close();
}
}
static public void Update(string oldname, string newname)
{
try
{
connection.Open();
SqlCeCommand commandInsert = new SqlCeCommand("update [Table] set username = @newname where username = @oldname", connection);
commandInsert.Parameters.Add("@newname", newname);
commandInsert.Parameters.Add("@oldname", oldname);
commandInsert.ExecuteNonQuery();
}
catch (SqlCeException ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
connection.Close();
}
}
}
}
Form1 class:
private void Form1_Load(object sender, EventArgs e)
{
SQLFunctions.Refresh(this.dataGridView1);
}
private void button1_Click(object sender, EventArgs e)
{
if (textBox1.Text != "")
{
SQLFunctions.Insert(textBox1.Text);
SQLFunctions.Refresh(this.dataGridView1);
}
}
private void button2_Click(object sender, EventArgs e)
{
if (textBox2.Text != "")
{
SQLFunctions.Delete(textBox2.Text);
SQLFunctions.Refresh(this.dataGridView1);
}
}
private void button3_Click(object sender, EventArgs e)
{
if (textBox3.Text != "" && textBox4.Text != "")
{
SQLFunctions.Update(textBox3.Text, textBox4.Text);
SQLFunctions.Refresh(this.dataGridView1);
}
}
}
}
Result:
