Basic Insert,Update,Delete,Search in .NET ( CRUDE Operations)


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Configuration;

public partial class CRUDEOperations : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
//

}
protected void btnAdd_Click(object sender, EventArgs e)
{
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings[“Emp”].ConnectionString))
{
DataTable dt=new DataTable();
SqlDataAdapter da = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand();
da.InsertCommand = con.CreateCommand();
da.InsertCommand.CommandType = CommandType.Text;
da.InsertCommand.CommandText = “insert into EMP (empname,empcity) values(@empname,@empcity)”;
da.InsertCommand.Parameters.AddWithValue(“@empname”, txtempname.Text);
da.InsertCommand.Parameters.AddWithValue(“@empcity”, txtempcity.Text);
da.InsertCommand.Connection = con;
try
{
con.Open();
da.InsertCommand.ExecuteNonQuery();
Response.Write(“Record Inserted”);
con.Close();
}
catch (Exception ex)
{
throw;
}
finally
{
con.Close();
}
}
}

protected void btnshowdata_Click(object sender, EventArgs e)
{
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings[“Emp”].ConnectionString))
{
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = “Select * from EMP”;
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
da.SelectCommand = cmd;
try
{
con.Open();
da.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
}
catch (Exception ex)
{
throw ex;
con.Close();
}
finally
{
con.Close();
}

}//using block
}//end show

protected void btnDelete_Click(object sender, EventArgs e)
{
SqlCommand cmd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter();
using(SqlConnection con=new SqlConnection(ConfigurationManager.ConnectionStrings[“Emp”].ConnectionString))
{
da.DeleteCommand = con.CreateCommand();
da.DeleteCommand.CommandType=CommandType.StoredProcedure;
da.DeleteCommand.CommandText = “DeleteEMP”;
da.DeleteCommand.Parameters.AddWithValue(“@EmployeeID”, TextBox1.Text);
da.DeleteCommand.Connection=con;
try
{
con.Open();
da.DeleteCommand.ExecuteNonQuery();
Response.Write(“record Deleted”);
con.Close();
}
catch(Exception ec)
{
}
finally
{
con.Close();
}
}//using end here
}/method delete

protected void btnSearch_Click(object sender, EventArgs e)
{
SqlCommand cmd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter();
DataTable dt = new DataTable();
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings[“Emp”].ConnectionString))
{
try
{ da.SelectCommand = con.CreateCommand();
da.SelectCommand.CommandType = CommandType.Text;
da.SelectCommand.CommandText = “Select *from EMP where EMPID=@EMPID”;
da.SelectCommand.Parameters.AddWithValue(“EMPID”, TextBox1.Text);
da.SelectCommand.Connection = con;
con.Open();
da.Fill(dt);
Response.Write(“Reocord Found”);
GridView1.DataSource = dt;
GridView1.DataBind();
con.Close();
}
catch (Exception ex)
{

}

finally
{
con.Close();
}
}
}
protected void btnWebService_Click(object sender, EventArgs e)
{
dtWebService ws = new dtWebService();
DataTable dt= ws.getWebseriviceData();
GridView1.DataSource = dt;
Response.Write(“Web Service Called…!” + ws.HelloWorld());
GridView1.DataBind();

}
protected void btnSQLDataSource_Click(object sender, EventArgs e)
{
using (SqlDataSource datasource = new SqlDataSource())
{
datasource.ConnectionString = ConfigurationManager.ConnectionStrings[“EMP”].ConnectionString;
datasource.SelectCommandType = SqlDataSourceCommandType.StoredProcedure;
datasource.SelectCommand = “SelectEmp”;
datasource.Select(DataSourceSelectArguments.Empty);
DataTable dt = ((DataView)datasource.Select(DataSourceSelectArguments.Empty)).Table;
GridView1.DataSource = dt;
GridView1.DataBind();
}
}
}

############## Find Record ############
SqlCommand cmd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter();
DataTable dt = new DataTable();
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings[“Emp”].ConnectionString))
{
try
{
da.SelectCommand = con.CreateCommand();
da.SelectCommand.CommandType = CommandType.Text;
da.SelectCommand.CommandText = “Select *from EMP where EMPID=@EMPID”;
da.SelectCommand.Parameters.AddWithValue(“EMPID”, TextBox1.Text);
da.SelectCommand.Connection = con;
con.Open();
da.Fill(dt);
Response.Write(“Reocord Found”);
GridView1.DataSource = dt;
GridView1.DataBind();
con.Close();
}
catch (Exception ex)
{

}
finally
{
con.Close();
}
}
############ Use StoredProcedure ########
SqlCommand cmd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter();
using(SqlConnection con=new SqlConnection(ConfigurationManager.ConnectionStrings[“Emp”].ConnectionString))
{
da.DeleteCommand = con.CreateCommand();
da.DeleteCommand.CommandType=CommandType.StoredProcedure;
da.DeleteCommand.CommandText = “DeleteEMP”;
da.DeleteCommand.Parameters.AddWithValue(“@EmployeeID”, TextBox1.Text);
da.DeleteCommand.Connection=con;
try
{
con.Open();
da.DeleteCommand.ExecuteNonQuery();
Response.Write(“record Deleted”);
con.Close();
}
catch(Exception ec)
{
}
finally
{
con.Close();
}
############  Insert Record ############
DataTable dt=new DataTable();
SqlDataAdapter da = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand();
da.InsertCommand = con.CreateCommand();
da.InsertCommand.CommandType = CommandType.Text;
da.InsertCommand.CommandText = “insert into EMP (empname,empcity) values(@empname,@empcity)”;

da.InsertCommand.Parameters.AddWithValue(“@empname”, txtempname.Text);
da.InsertCommand.Parameters.AddWithValue(“@empcity”, txtempcity.Text);
da.InsertCommand.Connection = con;

try
{
con.Open();
da.InsertCommand.ExecuteNonQuery();

//cmd.ExecuteNonQuery();
Response.Write(“Record Inserted”);
con.Close();
}
catch (Exception ex)
{
throw;

}
finally
{
con.Close();

}

##############  Delete Command ############
SqlCommand cmd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter();
using(SqlConnection con=new SqlConnection(ConfigurationManager.ConnectionStrings[“Emp”].ConnectionString))
{

da.DeleteCommand = con.CreateCommand();
da.DeleteCommand.CommandType=CommandType.StoredProcedure;
da.DeleteCommand.CommandText = “DeleteEMP”;
da.DeleteCommand.Parameters.AddWithValue(“@EmployeeID”, TextBox1.Text);
da.DeleteCommand.Connection=con;
try
{
con.Open();
da.DeleteCommand.ExecuteNonQuery();
Response.Write(“record Deleted”);
con.Close();
}
catch(Exception ec)
{

}
finally
{
con.Close();
}
}//using end here

############ Select Data #############
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings[“Emp”].ConnectionString))
{
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = “Select * from EMP”;
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
da.SelectCommand = cmd;
try
{
con.Open();
da.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();

}
catch (Exception ex)
{
throw ex;
con.Close();

}
finally
{

con.Close();
}

}//using block
############## Create Webservice ##########
[WebMethod]
public DataTable getWebseriviceData()
{
DataTable dt=new DataTable(“Test”);
SqlCommand cmd=new SqlCommand();
SqlDataAdapter da=new SqlDataAdapter();
using(SqlConnection con=new SqlConnection(ConfigurationManager.ConnectionStrings[“EMP”].ConnectionString))
{
da.SelectCommand = con.CreateCommand();
da.SelectCommand.CommandType = CommandType.Text;
da.SelectCommand.CommandText = “Select *from EMP”;
da.SelectCommand.Connection = con;
try
{
con.Open();
da.Fill(dt);

con.Close();
}
catch (Exception ec)
{

}
finally
{

con.Close();
}

}
return dt;
}

############# End #############

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s