September 18, 2013

Example of code of c sharp with SQL Server connectivity


 using System;  
  using System.Data;  
  using System.Text;  
  using System.Windows.Forms;  
  using System.Data.SqlClient;  
  using System.Drawing;  
  namespace DatabaseExp  
  {  
  public partial class DatabaseExp : Form  
  {  
  SqlConnection conn;  
  SqlDataAdapter adaptr;  
  SqlCommand cmd;  
  DataTable tbl;  
  static int count = 0, current = 0;  
  static int check = 0;  
  public DatabaseExp()  
  {  
  InitializeComponent();  
 }  
  private void DatabaseExp_Load(object sender, EventArgs e)  
  {  
  string connectionString = null;  
  adaptr = new SqlDataAdapter();  
  tbl = new DataTable();  
  connectionString = "Data Source=127.0.0.1;Initial Catalog=Abdul;User ID=sa;Password=allah";  
  conn = new SqlConnection(connectionString);  
  try  
  {  
  conn.Open();  
  MessageBox.Show("Connection Open !", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);  
  //conn.Close();  
  }  
  catch (Exception ex)  
  {  
  MessageBox.Show("Can not open connection ! ");  
  }  
  }  
 private void cmd_Add_Click(object sender, EventArgs e)  
  {  
  string sql;  
  sql = "Insert INTO Student VALUES('" + tRoll.Text + "','" + tName.Text + "','" + tAddress.Text + "')";  
  try  
  {  
  cmd = new SqlCommand(sql, conn);  
  cmd.ExecuteNonQuery();  
  MessageBox.Show("Record Inserted Successfully", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);  
  cmd.Dispose();  
  Refresh();  
  }  
  catch (Exception ex)  
  {  
  MessageBox.Show(ex.Message);  
  }  
  }  
 private void cmd_Refresh_Click(object sender, EventArgs e)  
  {  
  Refresh();  
  }  
 private void Refresh()  
  {  
  adaptr.Dispose();  
  tbl.Reset();  
  adaptr.SelectCommand = new SqlCommand("Select * from Student", conn);  
  adaptr.Fill(tbl);  
  count = tbl.Rows.Count;  
  current = 0;  
  if (count > 0)  
  Display(tbl);  
  else  
  MessageBox.Show("No Records In The Table", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);  
 }  
  private void Display(DataTable tbl)  
  {  
  tRoll.Text = tbl.Rows[current]["Roll Number"].ToString();  
  tName.Text = tbl.Rows[current]["Name"].ToString();  
  tAddress.Text = tbl.Rows[current]["Address"].ToString();  
  }  
 private void cmd_Clear_Click(object sender, EventArgs e)  
  {  
  tRoll.Text = "";  
  tName.Text = "";  
  tAddress.Text = "";  
  }  
 private void cmd_Delete_Click(object sender, EventArgs e)  
  {  
  if (MessageBox.Show("Do You Want to Delete This Record?", "Confirm delete", MessageBoxButtons.YesNo) == DialogResult.Yes)  
  {  
  string sql;  
  string key = tRoll.Text.ToString();  
  sql = "Delete From Student Where [Roll Number]='" + key + "'";  
  try  
  {  
  cmd = new SqlCommand(sql, conn);  
  cmd.ExecuteNonQuery();  
  MessageBox.Show("Record Deleted Successfully", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);  
  cmd.Dispose();  
  Refresh();  
  }  
  catch (Exception ex)  
  {  
  MessageBox.Show(ex.Message);  
  }  
  }  
  }  
  private void cmd_Search_Click(object sender, EventArgs e)  
  {  
  string sql;  
  string nm = tName.Text.ToString();  
  sql = "Select * From Student Where [Name]='" + nm + "'";  
  adaptr.Dispose();  
  tbl.Reset();  
  adaptr.SelectCommand = new SqlCommand(sql, conn);  
  adaptr.Fill(tbl);  
  count = tbl.Rows.Count;  
  current = 0;  
  //MessageBox.Show(count + "");  
  if (count > 0)  
  Display(tbl);  
  else  
  MessageBox.Show("No Record In The Table", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);  
  }  
 private void cmd_Previous_Click(object sender, EventArgs e)  
  {  
  if (current > 0)  
  {  
  current--;  
  Display(tbl);  
  }  
  else  
  {  
  current = count - 1;  
  Display(tbl);  
  }  
  }  
  private void cmd_Next_Click(object sender, EventArgs e)  
  {  
  if (current == count - 1)  
  {  
  current = 0;  
  Display(tbl);  
  }  
  else  
  {  
  current++;  
  Display(tbl);  
  }  
  }  
 private void cmd_Cancel_Click(object sender, EventArgs e)  
  {  
  if (check == 1)  
  {  
  Button_Status(true);  
  check = 0;  
  }  
  else  
  {  
  try  
  {  
  conn.Close();  
  this.Dispose();  
  }  
  catch (Exception ex)  
  {  
  MessageBox.Show(ex.Message);  
  }  
  }  
  }  
 private void Button_Status(Boolean value)  
  {  
  cmd_Add.Enabled = value;  
  cmd_Refresh.Enabled = value;  
  cmd_Clear.Enabled = value;  
  cmd_Delete.Enabled = value;  
  cmd_Previous.Enabled = value;  
  cmd_Next.Enabled = value;  
 }  
  private void cmd_Edit_Click(object sender, EventArgs e)  
  {  
  check = 1;  
  Button_Status(false);  
  tRoll.Enabled = false;  
  }  
 private void cmd_Save_Click(object sender, EventArgs e)  
  {  
  string sql;  
  string key = tRoll.Text.ToString();  
  if (check == 1)  
  {  
  check = 0;  
  tRoll.Enabled = true;  
  sql = "Update Student Set [Name]='" + tName.Text + "',[Address]='" + tAddress.Text + "' Where [Roll Number]='" + key + "'";  
  try  
  {  
  cmd = new SqlCommand(sql, conn);  
  cmd.ExecuteNonQuery();  
  MessageBox.Show("Record Edited Successfully", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);  
  cmd.Dispose();  
  Button_Status(true);  
  tbl.Reset();  
  Refresh();  
  }  
  catch (Exception ex)  
  {  
  MessageBox.Show(ex.Message);  
  }  
  }  
  }  
 }  
  }