Add, delete, modify, and query databases using datasets and data adapters

Using System;
Using System. Data;
Using System. Configuration;
Using System. Collections;
Using System. Web;
Using System. Web. Security;
Using System. Web. UI;
Using System. Web. UI. WebControls;
Using System. Web. UI. WebControls. WebParts;
Using System. Web. UI. HtmlControls;
Using System. Data. SqlClient; // import the namespace

Public partial class chap09_DataAccessDemo: System. Web. UI. Page
{
Protected void Page_Load (object sender, EventArgs e)
{

}
Protected void btnQuery_Click (object sender, EventArgs e)
{
SqlDataAdapter da = new SqlDataAdapter ();

// Create a connection object
SqlConnection conn = new SqlConnection ();
Conn. ConnectionString = "Data Source = LIYONGPING; Initial Catalog = BookShop; User ID = sa; Password = 2008aozheng @) ^ ";
// Create a command object
SqlCommand selectCmd = new SqlCommand ();
SelectCmd. CommandText = "select * from Books ";
SelectCmd. Connection = conn;

// Set the SelectCommand attribute of the data adapter
Da. SelectCommand = selectCmd;

// Create a DataSet object
DataSet data = new DataSet ();
// Use the data adapter to fill the dataset
Da. Fill (data, "Books ");

// Display the data on the page's GridView Control
GridView1.DataSource = data. Tables ["Books"];
GridView1.DataBind ();
}
Protected void btnAdd_Click (object sender, EventArgs e)
{
// Step 1: verify the data ......
//
SqlDataAdapter da = new SqlDataAdapter ();
// Create a connection object
SqlConnection conn = new SqlConnection ();
Conn. ConnectionString = "Data Source = LIYONGPING; Initial Catalog = BookShop; User ID = sa; Password = 2008aozheng @) ^ ";
// Create a query command object
SqlCommand selectCmd = new SqlCommand ();
SelectCmd. CommandText = "select * from Books ";
SelectCmd. Connection = conn;

// Create a command object for adding data
SqlCommand insertCmd = new SqlCommand ();
InsertCmd. CommandText = "insert into Books values (@ BookName, @ Author, @ Publisher, @ UnitPrice, @ Discount, @ BookImageFileName )";
InsertCmd. Connection = conn;
// Add parameters to the INSERT command
/*
SqlParameter bookNameParam = new SqlParameter ("@ BookName", SqlDbType. NVarChar, 50 );
BookNameParam. SourceColumn = "BookName ";
InsertCmd. Parameters. Add (bookNameParam );
*/
InsertCmd. Parameters. Add ("@ BookName", SqlDbType. NVarChar, 50, "BookName ");
InsertCmd. Parameters. Add ("@ Author", SqlDbType. NVarChar, 50, "Author ");
InsertCmd. Parameters. Add ("@ Publisher", SqlDbType. NVarChar, 50, "Publisher ");
InsertCmd. Parameters. Add ("@ UnitPrice", SqlDbType. Money, 8, "UnitPrice ");
InsertCmd. Parameters. Add ("@ Discount", SqlDbType. NVarChar, 8, "Discount ");
InsertCmd. Parameters. Add ("@ BookImageFileName", SqlDbType. NVarChar, 50, "BookImageFileName ");
// Set the SelectCommand attribute of the data adapter/InsertCommand
Da. SelectCommand = selectCmd;
Da. InsertCommand = insertCmd;
// Create a DataSet object
DataSet data = new DataSet ();
// Use the data adapter to fill the data adapter
Da. Fill (data, "Books ");

// Add a record to the DataSet Books table
DataRow drNew = data. Tables ["Books"]. NewRow ();
// Set the value of the newly added row
DrNew ["BookName"] = txtBookName. Text;
DrNew ["Author"] = txtAuthor. Text;
DrNew [3] = txtPublishName. Text;
DrNew [4] = txtPrice. Text;
DrNew [5] = txtDiscount. Text;
DrNew [6] = txtFileName. Text;
// Add rows to the table
Data. Tables ["Books"]. Rows. Add (drNew );
// Update the data to the database through the data adapter
Da. Update (data, "Books ");
}
Protected void btnEdit_Click (object sender, EventArgs e)
{
// Step 1: verify the data ......
//
SqlDataAdapter da = new SqlDataAdapter ();
// Create a connection object
SqlConnection conn = new SqlConnection ();
Conn. ConnectionString = "Data Source = LIYONGPING; Initial Catalog = BookShop; User ID = sa; Password = 2008aozheng @) ^ ";
// Create a query command object
SqlCommand selectCmd = new SqlCommand ();
SelectCmd. CommandText = "select * from Books ";
SelectCmd. Connection = conn;

// Create a command object for modifying data
SqlCommand editCmd = new SqlCommand ();
EditCmd. CommandText = "update Books set Discount = @ Discount where BookID = @ BookID ";
EditCmd. Connection = conn;
// Add parameters to the modify data command

EditCmd. Parameters. Add ("@ BookID", SqlDbType. Int, 4, "BookID ");
EditCmd. Parameters. Add ("@ Discount", SqlDbType. Float, 8, "Discount ");
// EditCmd. Parameters ["@ BookID"]. SourceVersion = DataRowVersion. Original;
// Set the SelectCommand attribute/UpdateCommand attribute of the data adapter
Da. SelectCommand = selectCmd;
Da. UpdateCommand = editCmd;
// Create a DataSet object
DataSet data = new DataSet ();
// Use the data adapter to fill the data adapter
Da. Fill (data, "Books ");

// Find the row to be modified

DataRow [] drSelect = data. Tables ["Books"]. Select ("BookID =" + txtBookIDForEdit. Text. Trim ());
DataRow editDr;
If (drSelect. Length> 0)
{
EditDr = drSelect [0];
}
Else
{
Return;
}


/*
Data. Tables ["Books"]. PrimaryKey = new DataColumn [] {data. Tables ["Books"]. Columns ["BookID"]};
DataRow editDr = data. Tables ["Books"]. Rows. Find (int. Parse (txtBookIDForEdit. Text ));
If (editDr = null)
Return;
*/
/*
Int nBookID = int. Parse (txtBookIDForEdit. Text );

DataRow editDr = null;
Foreach (DataRow dr in data. Tables ["Books"]. Rows)
{
If (int. Parse (dr ["BookID"]. ToString () = nBookID)
{
EditDr = dr;
Break;
}
}
*/
EditDr ["Discount"] = txtNewDiscount. Text;

// Update the data to the database through the data adapter
Da. Update (data, "Books ");
}
Protected void btnDel_Click (object sender, EventArgs e)
{
// Step 1: verify the data ......
//
SqlDataAdapter da = new SqlDataAdapter ();
// Create a connection object
SqlConnection conn = new SqlConnection ();
Conn. ConnectionString = "Data Source = LIYONGPING; Initial Catalog = BookShop; User ID = sa; Password = 2008aozheng @) ^ ";
// Create a query command object
SqlCommand selectCmd = new SqlCommand ();
SelectCmd. CommandText = "select * from Books ";
SelectCmd. Connection = conn;

// Create a command object for deleting data
SqlCommand delCmd = new SqlCommand ();
DelCmd. CommandText = "delete Books where BookID = @ BookID ";
DelCmd. Connection = conn;
// Add parameters to the data deletion command

DelCmd. Parameters. Add ("@ BookID", SqlDbType. Int, 4, "BookID ");

// EditCmd. Parameters ["@ BookID"]. SourceVersion = DataRowVersion. Original;
// Set the SelectCommand attribute of the data adapter/DeleteCommand attribute
Da. SelectCommand = selectCmd;
Da. DeleteCommand = delCmd;
// Create a DataSet object
DataSet data = new DataSet ();
// Use the data adapter to fill the data adapter
Da. Fill (data, "Books ");

Int nBookID = int. Parse (txtBookIDForDel. Text );
DataRow delDr = null;
Foreach (DataRow dr in data. Tables ["Books"]. Rows)
{
If (int. Parse (dr ["BookID"]. ToString () = nBookID)
{
DelDr = dr;
Break;
}
}
If (delDr! = Null)
{
DelDr. Delete (); // Delete and change rows from the dataset

// Update the data to the database through the data adapter
Da. Update (data, "Books ");
}
}
}