Insert , Delete , View In Console Application........
class Program
{
public static void insert()
{
SqlConnection con = new SqlConnection("Data Source=ARVIND-KATARIA\\SQLEXPRESS;Initial Catalog=practice;Integrated Security=True");
SqlCommand com = new SqlCommand();
com.Connection = con;
com.CommandType = CommandType.Text;
Console.WriteLine("Enter student id");
int id = Convert.ToInt16(Console.ReadLine());
Console.WriteLine("Enter student Name");
string name = Convert.ToString(Console.ReadLine());
com.CommandText = "insert into stu1(ID,Name)values(@param1,@param2)";
com.Parameters.AddWithValue("@param1", id);
com.Parameters.AddWithValue("@param2", name);
con.Open();
SqlDataReader datareader = com.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(datareader);
Console.WriteLine("\n Record insert ");
}
public static void display()
{
SqlConnection con = new SqlConnection("Data Source=ARVIND-KATARIA\\SQLEXPRESS;Initial Catalog=practice;Integrated Security=True");
con.Open();
DataSet ds = new DataSet();
try
{
string sql = "select * from stu1";
SqlCommand cmd = new SqlCommand(sql, con);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
printrow(Convert.ToString(dr["ID"]), Convert.ToString(dr["Name"]));
}
con.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
static void printline()
{
Console.WriteLine(new string('-', 80));
}
static void printrow(string ID, string Name)
{
Console.WriteLine(string.Format("|{0}|{1}|", AlignCenter(ID, 17), AlignCenter(Name, 17)));
}
static string AlignCenter(string text, int width)
{
if (string.IsNullOrEmpty(text))
{
return new string(' ', width);
}
else
{
return text.PadRight(width - (width - text.Length) / 2).PadLeft(width);
}
}
public static void delete()
{
SqlConnection con = new SqlConnection("Data Source=ARVIND-KATARIA\\SQLEXPRESS;Initial Catalog=practice;Integrated Security=True");
SqlCommand com = new SqlCommand();
com.Connection = con;
com.CommandType = CommandType.Text;
Console.WriteLine("Enter student id");
string id1 = Convert.ToString(Console.ReadLine());
Console.WriteLine("Enter student Name");
string name1 = Convert.ToString(Console.ReadLine());
com.CommandText = string.Format("delete from stu1 where ID={0}", id1);
com.Parameters.AddWithValue("@param1", "id");
con.Open();
SqlDataReader datareader = com.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(datareader);
com.ExecuteNonQuery();
Console.WriteLine("Data Deleated");
com.CommandText = ("select * from stu1");
Console.WriteLine("Detail Of the New Data Of student ");
foreach (DataColumn colum in dt.Columns)
{
Console.WriteLine(string.Format("{0}", colum.ColumnName));
}
foreach (DataRow row in dt.Rows)
{
Console.WriteLine(string.Format("{0}\t\t{1}", row["ID"], row["Name"]));
}
dt.Load(datareader);
}
public static void update()
{
string connString = "Data Source=ARVIND-KATARIA\\SQLEXPRESS;Initial Catalog=practice;Integrated Security=True";
string qry = @"select * from stu1 ";
string upd = @"update stu1 set Name = @Name where ID = @ID";
SqlConnection conn = new SqlConnection(connString);
try {
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = new SqlCommand(qry, conn);
DataSet ds = new DataSet();
da.Fill(ds, "stu1");
DataTable dt = ds.Tables["stu1"];
dt.Rows[0]["Name"] = "W";
foreach (DataRow row in dt.Rows)
{
Console.WriteLine(
"{0} ",
row["Name"].ToString().PadRight(15));
}
// Update employees
SqlCommand cmd = new SqlCommand(upd, conn);
cmd.Parameters.Add("@Name",SqlDbType.NVarChar,50, "Name");
SqlParameter parm = cmd.Parameters.Add("@ID",SqlDbType.Int,4,"ID");
parm.SourceVersion = DataRowVersion.Original;
da.UpdateCommand = cmd;
da.Update(ds, "stu1");
} catch(Exception e) {
Console.WriteLine("Error: " + e);
} finally {
conn.Close();
}
}
static void Main(string[] args)
{
int ch;
do
{
Console.WriteLine("\n ---------- MENU --------------");
Console.WriteLine("\n 1. INSERT ");
Console.WriteLine("\n 2. DISPLAY");
Console.WriteLine("\n 3. DELETE ");
Console.WriteLine("\n 4. UPDATE ");
Console.WriteLine("\n 5. EXIT");
Console.WriteLine("\n ENTER YOUR CHOICE");
string s = Convert.ToString(Console.ReadLine());
ch = int.Parse(s);
if (ch == 1)
{
insert();
}
else if (ch == 2)
{
display();
printrow("ID","Name");
printline();
Console.ReadKey();
}
else if (ch == 3)
{
delete();
}
else if (ch == 4)
{
update();
}
else if (ch == 5)
{
Console.WriteLine("\n Good Byeeeeeee");
}
} while (ch != 4);
}
}
BY:- Kataria Arvind...!!!!!!
