using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Drawing.Drawing2D;
using System.Drawing.Design;
using System.Drawing.Printing;
/// <summary>
/// Summary description for DataUtility
/// </summary>
using class AllConnection
{
# region All Module level variables
SqlDataAdapter Da;
public SqlConnection cnn;
SqlCommand Cmd;
DataSet ds;
# endregion
public void ConnectServer()
{
if (cnn == null)
{
cnn = new SqlConnection("Data Source=.;Initial Catalog=Hotel;Persist Security Info=True;User ID=sa;Password=server");
}
if (cnn.State == ConnectionState.Closed)
{
cnn.Open();
}
Cmd = new SqlCommand();
Cmd.Connection = cnn;
}
public void CloseConnection()
{
if (cnn.State == ConnectionState.Open)
{
cnn.Close();
}
}
/// <summary>
/// This is to release the memory from the connection object
/// </summary>
public void DisposeConnection()
{
if (cnn != null)
{
cnn.Dispose();
cnn = null;
}
}
public int ExecuteSql(String Sql)
{
ConnectServer();
//int Result;
//Set command object properties
Cmd.CommandType = CommandType.Text;
Cmd.CommandText = Sql;
Cmd.CommandTimeout = 3000;
// Now call the method
int Result = Cmd.ExecuteNonQuery();
CloseConnection();
DisposeConnection();
return Result;
}
public bool IsExist(string sql)
{
ConnectServer();
//now set command object properties
Cmd.CommandType = CommandType.Text;
Cmd.CommandText = sql;
Cmd.CommandTimeout = 2000;
//Execute Command object method
int Result = (int)Cmd.ExecuteScalar();
CloseConnection();
DisposeConnection();
if (Result > 0)
{
return true;
}
else
{
return false;
}
}
public SqlDataReader GetDataReader(String Sql)
{
ConnectServer();
SqlDataReader dReader;
Cmd.CommandType = CommandType.Text;
Cmd.CommandText = Sql;
Cmd.CommandTimeout = 1000;
dReader = Cmd.ExecuteReader();
//CommandBehavior.CloseConnection
return dReader;
}
public void charonly(KeyPressEventArgs e)
{
if (Char.IsNumber(e.KeyChar) || Char.IsSymbol(e.KeyChar) || Char.IsWhiteSpace(e.KeyChar) || Char.IsPunctuation(e.KeyChar))
{
MessageBox.Show("Only Char are allowed");
e.Handled = true;
}
}
public void charAndDigit(KeyPressEventArgs e)
{
if (e.KeyChar != 39)
{
e.Handled = false;
}
else
{
MessageBox.Show("Invalid Input");
e.Handled = true;
}
}
public void UpperCase(KeyPressEventArgs e)
{
try
{
e.KeyChar = Char.ToUpper(e.KeyChar);
}
catch { }
}
public void digitonly(KeyPressEventArgs e)
{
try
{
if (!(char.IsDigit(e.KeyChar) || char.IsControl(e.KeyChar)))
{
e.Handled = true;
MessageBox.Show("Enter only digit point.", "Alert!");
}
}
catch { }
}
public void digitAndDeclmal(KeyPressEventArgs e)
{
try
{
if (!(char.IsDigit(e.KeyChar) || char.IsControl(e.KeyChar) || char.IsPunctuation(e.KeyChar)))
{
e.Handled = true;
MessageBox.Show("Enter only digit and decimal point.", "Alert!");
}
}
catch { }
}
public DataTable GetDataTable(String sql)
{
DataTable dt = new DataTable();
try
{
ConnectServer();
Da = new SqlDataAdapter();
Cmd.CommandType = CommandType.Text;
Cmd.CommandText = sql;
Cmd.CommandTimeout = 2000;
Da.SelectCommand = Cmd;
//ds = new DataSet();
Da.Fill(dt);
//dt = ds.Tables["table"];
CloseConnection();
return dt;
}
catch
{
return dt;
}
}
public DataTable returnDataTable(string sql)
{
//Con.Open();
ds = new DataSet();
Da = new SqlDataAdapter(sql, cnn);
Da.Fill(ds, "datatable");
DataTable dt = new DataTable();
dt = ds.Tables["datatable"];
cnn.Close();
return (dt);
}
public void FillCombo(string Query, string datatext,string datavalue, ComboBox ComboBox1)
{
try
{
ConnectServer();
ComboBox1.Items.Clear();
ComboBox1.Text = "";
SqlCommand cmd = new SqlCommand(Query,cnn);
SqlDataAdapter ada = new SqlDataAdapter(cmd);
//SqlDataReader dr = cmd.ExecuteReader();
DataTable dt = new DataTable();
ada.Fill(dt);
//while (dr.Read())
//{
// ComboBox1.Items.Add(dr[0].ToString());
//}
ComboBox1.DataSource = dt;
ComboBox1.DisplayMember =datavalue ;
ComboBox1.ValueMember = datatext;
// dr.Close();
CloseConnection();
DisposeConnection();
}
catch (Exception)
{
// throw;
}
}
public void FillList(string Query, ListBox ListBox1)
{
ListBox1.Items.Clear();
ConnectServer();
SqlCommand cmd = new SqlCommand(Query, cnn);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
ListBox1.Items.Add(dr[0].ToString());
}
dr.Close();
CloseConnection();
DisposeConnection();
}
public void FillDataGridView(string Query, DataGridView DataGridView1)
{
ConnectServer();
SqlCommand cmd = new SqlCommand(Query, cnn);
SqlDataAdapter ada = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
ada.Fill(ds, "man");
DataGridView1.DataSource = ds;
DataGridView1.DataMember = "man";
CloseConnection();
DisposeConnection();
}
public void FillLabel(string Query, Label Label1)
{
ConnectServer();
Label1.Text = "";
SqlCommand cmd = new SqlCommand(Query, cnn);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Label1.Text = dr[0].ToString();
}
dr.Close();
CloseConnection();
DisposeConnection();
}
public void FillTextBox(string Query, TextBox TextBox1)
{
ConnectServer();
TextBox1.Text = "";
SqlCommand cmd = new SqlCommand(Query, cnn);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
TextBox1.Text = dr[0].ToString();
}
dr.Close();
CloseConnection();
DisposeConnection();
}
public void tb_KeyPress(object sender, KeyPressEventArgs e)
{
if (!char.IsControl(e.KeyChar)&& !char.IsDigit(e.KeyChar)&& e.KeyChar != '.')
{
e.Handled = true;
}
// only allow one decimal point
if (e.KeyChar == '.'
&& (sender as TextBox).Text.IndexOf('.') > -1)
{
e.Handled = true;
}
}
}
3. C sharp code write at the form1.cs file
Form1.CS Code like this:
