Code:
.CS File
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Data.SqlClient;
namespace DataBase_UserDetails
{
public partial class Form1 : Form
{
string connectionString;
public Form1()
{
InitializeComponent();
connectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=Database1 - Copy.mdf;Integrated Security=True;User Instance=True";
}
//Create a New Table
private void btnCreate_Click(object sender, EventArgs e)
{
SqlConnection SqlCon = new SqlConnection(connectionString);
SqlCon.Open(); //open the connection
string commandStr = "CREATE TABLE users("+
"user_id INT NOT NULL," +
"user_name VARCHAR(40) NOT NULL," +
"user_email VARCHAR(40) NOT NULL," +
"user_pass VARCHAR(40) NOT NULL," +
"user_registration_date DATETIME NOT NULL," +
"user_state VARCHAR(10) NOT NULL," +
"PRIMARY KEY(user_id))";
SqlCommand cmd = new SqlCommand(commandStr, SqlCon);
try
{
cmd.ExecuteNonQuery();
SqlCon.Close();
}
catch (Exception error)
{
MessageBox.Show(error.Message, "Error",MessageBoxButtons.OK,MessageBoxIcon.Error);
}
}
//Create a DataBase
private void btn_newDB_Click(object sender, EventArgs e)
{
string commandStr = "CREATE DATABASE Core";
SqlCommand cmd = new SqlCommand(commandStr);
try
{
cmd.ExecuteNonQuery();
}
catch (Exception error)
{
MessageBox.Show(error.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
//create new field
private void btnInsert_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(connectionString);
con.Open(); //open the connection
string commandStr = "INSERT INTO users(user_id, user_name, user_email, user_pass, user_registration_date, user_state)" +
" VALUES(@userID,@userName,@email,@userPass,@date,@userState)";
SqlCommand cmd = new SqlCommand(commandStr, con);//create an object from the sqlcommand
cmd.Parameters.AddWithValue("@userID", txtID.Text);
cmd.Parameters.AddWithValue("@userName", txtName.Text);
cmd.Parameters.AddWithValue("@email", txtEmail.Text);
cmd.Parameters.AddWithValue("@userPass", txtPass.Text);
cmd.Parameters.AddWithValue("@date", DateTime.Now);
cmd.Parameters.AddWithValue("@userState", txtState.Text);
try
{
cmd.ExecuteNonQuery();
con.Close();
}
catch (Exception error)
{
MessageBox.Show(error.Message);
}
}
//UPDATE
private void btnUpdate_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(connectionString);
con.Open();//open the connection between the database and the application
string commandString = "UPDATE users SET "+
"user_name=@userName,user_email=@email,user_pass=@userPass,"+
"user_registration_date=@date,user_state=@userState WHERE user_id=@selectedID";
string selectedID = treeView1.SelectedNode.Text;//get the selected index value
SqlCommand cmd = new SqlCommand(commandString, con);//make the SQL command object
cmd.Parameters.AddWithValue("@selectedID", selectedID);//replace the @selectedId with selected value on treenode
//get the selected index value from the treeview
//and then replace that with SQL string command
//therefore the condition is id everything related that ID will be updated.
//following fields will replace the txt boxes with SQL command parameters
cmd.Parameters.AddWithValue("@userName",txtName.Text);
cmd.Parameters.AddWithValue("@email",txtEmail.Text);
cmd.Parameters.AddWithValue("@userPass",txtPass.Text);
cmd.Parameters.AddWithValue("@date",DateTime.Now);
cmd.Parameters.AddWithValue("@userState",txtState.Text);
try
{
cmd.ExecuteNonQuery();//execute the command
con.Close();
}
catch (Exception error)
{
MessageBox.Show(error.Message);
}
}
//SELECT ALL
private void btnSelectAll_Click(object sender, EventArgs e)
{
treeView1.Nodes.Clear();//clear all nodes if there are any remaining
SqlConnection con = new SqlConnection(connectionString);
con.Open();
string commandString = "SELECT * FROM users";//get everything from the users table
SqlCommand cmd = new SqlCommand(commandString, con);
SqlDataReader reader;
try
{
reader = cmd.ExecuteReader();
while (reader.Read())
{
TreeNode mainNode = treeView1.Nodes.Add(reader["user_id"].ToString());
mainNode.Nodes.Add(reader["user_name"].ToString());
mainNode.Nodes.Add(reader["user_email"].ToString());
mainNode.Nodes.Add(reader["user_pass"].ToString());
mainNode.Nodes.Add(reader["user_registration_date"].ToString());
mainNode.Nodes.Add(reader["user_state"].ToString());
}
}
catch (Exception error)
{
MessageBox.Show(error.Message);
}
}
//Delete data Field
private void btnDelete_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(connectionString);
con.Open();
string commandStr = "DELETE FROM users WHERE user_id=@userID";//just select the primary key so it will delete the entire row
SqlCommand cmd = new SqlCommand(commandStr, con);
cmd.Parameters.AddWithValue("userID", treeView1.SelectedNode.Text);
try
{
cmd.ExecuteNonQuery();
con.Close();
}
catch (Exception error)
{
MessageBox.Show(error.Message);
}
}
//Select the TreeNode
private void treeView1_AfterSelect(object sender, TreeViewEventArgs e)
{
TreeNode indexNode = treeView1.SelectedNode;//get the selected node
txtID.Text = indexNode.Text;//get the index id
txtName.Text = indexNode.Nodes[0].Text;
txtEmail.Text = indexNode.Nodes[1].Text;
txtPass.Text = indexNode.Nodes[2].Text;
txtDate.Text = indexNode.Nodes[3].Text;
txtState.Text = indexNode.Nodes[4].Text;
}
//search Keyword
//IN SQL SERVER EXPREE DON"T USE '' SINGLE QUOTATION MARKS OR % WHEN COMPARE LIKE OR NOT LIKE
//NEARLY EQUALITY
private void btnSearch_Click(object sender, EventArgs e)
{
/*
* user_name=@userName,user_email=@email,user_pass=@userPass,"+
"user_registration_date=@date,user_state=@userState
*/
SqlConnection con = new SqlConnection(connectionString);
con.Open();
string commandStr = "SELECT * FROM users WHERE (user_name LIKE @userName) OR" +
"(user_email LIKE @userEmail) OR (user_pass LIKE @userPass) OR " +
"(user_registration_date LIKE @userReg) OR (user_state LIKE @userState)";
SqlCommand cmd = new SqlCommand(commandStr, con);
//replace the parameters with the C# components
cmd.Parameters.AddWithValue("@userName", txtSearchKey.Text);
cmd.Parameters.AddWithValue("@userEmail", txtSearchKey.Text);
cmd.Parameters.AddWithValue("@userPass", txtSearchKey.Text);
cmd.Parameters.AddWithValue("@userReg", txtSearchKey.Text);
cmd.Parameters.AddWithValue("@userState", txtSearchKey.Text);
try
{
SqlDataReader reader = cmd.ExecuteReader(); ;
while (reader.Read())//read the next record if non of record found return 0
{
TreeNode mainNode = treeView1.Nodes.Add(reader["user_id"].ToString());
mainNode.Nodes.Add(reader["user_name"].ToString());
mainNode.Nodes.Add(reader["user_email"].ToString());
mainNode.Nodes.Add(reader["user_pass"].ToString());
mainNode.Nodes.Add(reader["user_registration_date"].ToString());
mainNode.Nodes.Add(reader["user_state"].ToString());
}
}
catch (Exception error)
{
MessageBox.Show(error.Message);
}
}
private void btnClear_Click(object sender, EventArgs e)
{
treeView1.Nodes.Clear();
}
}
}First add a Server SQL Database into the solution explorer and just proceed with above codes.!
Copyright Notice
All Source Codes belong to Core and don't mess with my codes without my permission.
I made this on another forum which I can't mention in here.
but thought make the same thing in here is useful.
