This is one of those tucked-away features in Visual Studio that, once you find it, can make you slap your forehead so hard that it hurts. Warning: You may need an aspirin after reading this.
Notes:
I'm using Microsoft Visual Studio Team System 2008. It also works with the Express editions (thanks to @bhitalks for checking this).
I'm also using SQL Server Express 2005. To get this to work on my system, I had to download and install: SQLSysClrTypes.msi.
Step 1:
Right mouse-click the App_Code folder in the application and select "Add New Item..." When the "Add New Item" window appears, select DataSet. It doesn't matter what you name the DataSet but remember it for later.
Step 2:
Right mouse click in the XSD window and select "Add, TableAdapter…"
Step 3:
After a few seconds, the "Choose Your Data Connection" window should appear. Choose, or create, a connection and click "Next".
Step 4:
Here's the magic. In the "Choose a Command Type" window, choose "Create new stored procedures" and click "Next."
Step 5:
Enter an SQL select statement for a single table and click "Next."
Step 6:
The "Create the Stored Procedures" window appears. Rename the stored procedures to something meaningful and click "Next", or go ahead and click "Finish." Since I selected the Employees table, I renamed the procedures with the Employee prefix. You can click Preview SQL Script to see what is going to be run:
Step 7:
You should see the Wizard Results window…click "Finish" one more time and the Stored Procedures will be created.
Step 8:
Delete the .XSD file from the App_Code directory that was created in Step 1. It's not needed.
You are done.
Here's how the generated Stored Procedures look in SQL Server Management Studio:
The generated SQL code is very clean. Of course, you can modify it to fit your requirements.
Now, ain't that better than typing them by hand?
I hope someone finds this useful.
Steve Wellens
Tuesday, December 29, 2009
Casting / Converting in .NET via ASP.NET Weblogs
Level
Beginner
Introduction
The basic thing at least you code is converting one type to other. There are so many methods and a dedicated class (Convert class) provided in .Net framework for the same. And Microsoft has provided better approaches and practices for efficient conversion like TryParse. Here, I am sharing my experience for casting and converting one type to other. In this post we see the basic data types like string, int and DateTime.
What are the problems?
Developers follow different approaches for getting the desired result. In my team I have seen the most usage of ToString. Whenever there is a need of string, we used to put .ToString() and for other data types, Convert.Whatever. Is it correct? Well, .Net will not have control on the input object what you have provided. I mean, it will throw compile errors if the expected type is not provided, but during runtime only it can say whether the provided input can be converted or not. Let’s assume a simple scenario. Let’s assume that this blog posts have post id and based on post id the data is retrieved. On click of any post, the page requests for corresponding post by passing this post id as query string or directly from URL(if is MVC). Since, I know the post id is integer, am using Convert.ToInt32 method to convert a string to int. This will work perfectly in normal conditions. But as said "Never trust external data", what happens, if the user changes id manually?? What happens if url weblogs.asp.net/fayaz/posts/csharp/123(typical MVC style) is modified to weblogs.asp.net/fayaz/posts/csharp/123abc?? Exception... And I feel application should not throw Exceptions for these small things. We can handle it in better way and can pass a message to user that invalid id is provided or some better way of handling.
Let’s Start
Let’s see the better approach for string, int and date time.
to String
The most common operation performed by any developer is converting a type to String. This can be offered to any type since all types are inherited from object and any object can be converted to string. Generally there are two ways to convert to string. One is ToString from Convert class and the other is ToString method in each type. If you search for what is the difference between Convert.ToString() and .ToString(), you will get tons of sites explaining the difference. And that will be the way of handling nulls. A Convert.ToString will return empty if the passed object is null, whereas .ToString() throws a NullReference exception, the most loved "Object reference not set to an instance of an object".
string nullString = null;
string resultString = string.Empty;
resultString = Convert.ToString(nullString); // returns empty
resultString = nullString.ToString(); // throws NullReferenceException
Obviously, we should use Convert.ToString where there are chances of null, like casting a session or viewstate to string. There are lots of scenarios like everywhere we can expect null. So, use Convert.ToString. Do you like ToString more than Convert.ToString?? Well, I love ToString and am used to it. Typing Convert.ToString and using every where is pretty difficult for me. Then I followed ExtensionMethods(framework should be 3.0 or Higher). I wrote an extension method for the same.
public static class ExtensionMethods
{
public static string ToStringOrEmpty(this object value)
{
return value == null ? string.Empty : value.ToString();
}
}
If you use exntension methods, then the code snippet would be...
string nullString = null;
string resultString = nullString.ToStringOrEmpty();
to Int
We can convert to int by using Convert.ToInt32 and int.Parse. The difference would be the same as we discussed in ToString. If null is passed to Convert.ToInt32, it returns default of int i.e., 0(zero). Whereas int.Parse throws ArguementNull exception. The difference is clear. Convert.ToInt32 has 19 over loaded methods including object, where as int.Parse has only 4 overloaded methods (no object). So, Convert.ToInt32 can handle null also.
string nullString = null;
int resultInt;
resultInt = Convert.ToInt32(nullString); // returns 0
resultInt = int.Parse(nullString); // throws ArguementNullException
Ok. We handled null a better way. Is it enough?? What if the value is some text which cannot be converted to int?? Is Convert.ToInt32 is handling this?? No, if you are passing an invalid text to convert it into int, then we get Format exception, "Input string was not in a correct format."
string invalidInt = "abc";
int resultInt;
resultInt = Convert.ToInt32(invalidInt); // throws FormatException
resultInt = int.Parse(invalidInt); // throws FormatException
Here it comes, the wonderful feature "TryParse". TryParse is similar to int.Parse, except it does not throw exception if the conversion fails.
string invalidInt = "abc";
int resultInt;
bool isInteger = int.TryParse(invalidInt, out resultInt); // resultInt = 0, isInteger = false
Check, if the return bool is true. If false, say that, the input is invalid.
If you use .net 3.0 or higher, then define your own extension method, ToInt
public static class ExtensionMethods
{
public static bool IsInt(this string value)
{
int num;
return int.TryParse(value, out num);
}
public static int ToInt(this string value)
{
int num;
int.TryParse(value, out num);
return num;
}
}
Here, we have two extension methods offered for a string. One is to check whether the input can be converted to int. And the other is returning int, if valid, else returning 0(zero).
to DateTime
This is similar to int discussion. So, am not going to elaborate. For null, Convert.ToDateTime returns default of DateTime, i.e., 1/1/0001 12:00:00 AM and DateTime.Parse throws exception.
string nullString = null;
DateTime resultDateTime;
resultDateTime = Convert.ToDateTime(nullString); // returns 1/1/0001 12:00:00 AM
resultDateTime = DateTime.Parse(nullString); // throws ArguementNullException
And for invalid data to be converted to DateTime...
string invalidDateTime = "12-12-12 12:12:12 AP";
DateTime resultDateTime;
bool isDateTime = DateTime.TryParse(invalidDateTime, out resultDateTime); // resultDateTime = 1/1/0001 12:00:00 AM, isDateTime = false
And Extension Methods will be
public static class ExtensionMethods
{
public static bool IsDateTime(this string value)
{
DateTime dt;
return DateTime.TryParse(value, out dt);
}
public static DateTime ToDateTime(this string value)
{
DateTime dt;
DateTime.TryParse(value, out dt);
return dt;
}
}
Conclusion
The views I have shared are better IMHO. They may not necessarily confirm to best practice. I am glad; if someone suggests me some better approaches. Will try to post converting objects from one form to other form shortly.
Beginner
Introduction
The basic thing at least you code is converting one type to other. There are so many methods and a dedicated class (Convert class) provided in .Net framework for the same. And Microsoft has provided better approaches and practices for efficient conversion like TryParse. Here, I am sharing my experience for casting and converting one type to other. In this post we see the basic data types like string, int and DateTime.
What are the problems?
Developers follow different approaches for getting the desired result. In my team I have seen the most usage of ToString. Whenever there is a need of string, we used to put .ToString() and for other data types, Convert.Whatever. Is it correct? Well, .Net will not have control on the input object what you have provided. I mean, it will throw compile errors if the expected type is not provided, but during runtime only it can say whether the provided input can be converted or not. Let’s assume a simple scenario. Let’s assume that this blog posts have post id and based on post id the data is retrieved. On click of any post, the page requests for corresponding post by passing this post id as query string or directly from URL(if is MVC). Since, I know the post id is integer, am using Convert.ToInt32 method to convert a string to int. This will work perfectly in normal conditions. But as said "Never trust external data", what happens, if the user changes id manually?? What happens if url weblogs.asp.net/fayaz/posts/csharp/123(typical MVC style) is modified to weblogs.asp.net/fayaz/posts/csharp/123abc?? Exception... And I feel application should not throw Exceptions for these small things. We can handle it in better way and can pass a message to user that invalid id is provided or some better way of handling.
Let’s Start
Let’s see the better approach for string, int and date time.
to String
The most common operation performed by any developer is converting a type to String. This can be offered to any type since all types are inherited from object and any object can be converted to string. Generally there are two ways to convert to string. One is ToString from Convert class and the other is ToString method in each type. If you search for what is the difference between Convert.ToString() and .ToString(), you will get tons of sites explaining the difference. And that will be the way of handling nulls. A Convert.ToString will return empty if the passed object is null, whereas .ToString() throws a NullReference exception, the most loved "Object reference not set to an instance of an object".
string nullString = null;
string resultString = string.Empty;
resultString = Convert.ToString(nullString); // returns empty
resultString = nullString.ToString(); // throws NullReferenceException
Obviously, we should use Convert.ToString where there are chances of null, like casting a session or viewstate to string. There are lots of scenarios like everywhere we can expect null. So, use Convert.ToString. Do you like ToString more than Convert.ToString?? Well, I love ToString and am used to it. Typing Convert.ToString and using every where is pretty difficult for me. Then I followed ExtensionMethods(framework should be 3.0 or Higher). I wrote an extension method for the same.
public static class ExtensionMethods
{
public static string ToStringOrEmpty(this object value)
{
return value == null ? string.Empty : value.ToString();
}
}
If you use exntension methods, then the code snippet would be...
string nullString = null;
string resultString = nullString.ToStringOrEmpty();
to Int
We can convert to int by using Convert.ToInt32 and int.Parse. The difference would be the same as we discussed in ToString. If null is passed to Convert.ToInt32, it returns default of int i.e., 0(zero). Whereas int.Parse throws ArguementNull exception. The difference is clear. Convert.ToInt32 has 19 over loaded methods including object, where as int.Parse has only 4 overloaded methods (no object). So, Convert.ToInt32 can handle null also.
string nullString = null;
int resultInt;
resultInt = Convert.ToInt32(nullString); // returns 0
resultInt = int.Parse(nullString); // throws ArguementNullException
Ok. We handled null a better way. Is it enough?? What if the value is some text which cannot be converted to int?? Is Convert.ToInt32 is handling this?? No, if you are passing an invalid text to convert it into int, then we get Format exception, "Input string was not in a correct format."
string invalidInt = "abc";
int resultInt;
resultInt = Convert.ToInt32(invalidInt); // throws FormatException
resultInt = int.Parse(invalidInt); // throws FormatException
Here it comes, the wonderful feature "TryParse". TryParse is similar to int.Parse, except it does not throw exception if the conversion fails.
string invalidInt = "abc";
int resultInt;
bool isInteger = int.TryParse(invalidInt, out resultInt); // resultInt = 0, isInteger = false
Check, if the return bool is true. If false, say that, the input is invalid.
If you use .net 3.0 or higher, then define your own extension method, ToInt
public static class ExtensionMethods
{
public static bool IsInt(this string value)
{
int num;
return int.TryParse(value, out num);
}
public static int ToInt(this string value)
{
int num;
int.TryParse(value, out num);
return num;
}
}
Here, we have two extension methods offered for a string. One is to check whether the input can be converted to int. And the other is returning int, if valid, else returning 0(zero).
to DateTime
This is similar to int discussion. So, am not going to elaborate. For null, Convert.ToDateTime returns default of DateTime, i.e., 1/1/0001 12:00:00 AM and DateTime.Parse throws exception.
string nullString = null;
DateTime resultDateTime;
resultDateTime = Convert.ToDateTime(nullString); // returns 1/1/0001 12:00:00 AM
resultDateTime = DateTime.Parse(nullString); // throws ArguementNullException
And for invalid data to be converted to DateTime...
string invalidDateTime = "12-12-12 12:12:12 AP";
DateTime resultDateTime;
bool isDateTime = DateTime.TryParse(invalidDateTime, out resultDateTime); // resultDateTime = 1/1/0001 12:00:00 AM, isDateTime = false
And Extension Methods will be
public static class ExtensionMethods
{
public static bool IsDateTime(this string value)
{
DateTime dt;
return DateTime.TryParse(value, out dt);
}
public static DateTime ToDateTime(this string value)
{
DateTime dt;
DateTime.TryParse(value, out dt);
return dt;
}
}
Conclusion
The views I have shared are better IMHO. They may not necessarily confirm to best practice. I am glad; if someone suggests me some better approaches. Will try to post converting objects from one form to other form shortly.
Friday, December 18, 2009
http://geekswithblogs.net/dotNETvinz/archive/2008/02.aspx
this is new asp.net blog
http://geekswithblogs.net/dotNETvinz/archive/2008/02.aspx
http://geekswithblogs.net/dotNETvinz/archive/2008/02.aspx
GridView Insert, Edit, Update and Delete – The Ado.Net way
http://geekswithblogs.net/dotNETvinz/archive/2009/02/22/gridview-insert-edit-update-and-delete--the-ado.net-way.aspx
GridView Insert, Edit, Update and Delete – The Ado.Net way
This demo is a continuation of my previous example “Binding GridView with Data”. If you are not familiar of binding the GridView the ado.net way then I would suggest looking at my previous example first before you proceed to this example.
Basically, this demo describes the basic way to do INSERT, EDIT, UPDATE and DELETE data in ASPNET GridView Control using the ADO.NET way.
STEP 1: Creating a DataBase Table
In this demo, I presumed that you already have a basic background on how to create a simple database table. In this example, this time I used my own database called SampleDB which has Customers Table and basically contains the following field columns:
CustomerID – PK
CompanyName
ContactName
ContactTitle
Address
Country
STEP 2: Setting Up the Connection String
connectionStrings
add name="DBConnection" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\SampleDB.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/
/connectionStrings
STEP 3: Setting up the GUI
Just for the simplicity of this demo, I set up the GUI like this:
html xmlns="http://www.w3.org/1999/xhtml"
head runat="server"
title
GridView Data Manipulation
/title
/head
body
form id="form1" runat="server"
div
table cellpadding="0" cellspacing="0"
tr
td style="width: 100px; height: 19px;"
Company ID
/td
td style="width: 100px; height: 19px;"
Company
/td>
td style="width: 100px; height: 19px;"
Name
/td
td style="width: 100px; height: 19px;"
Title
/td
td style="width: 100px; height: 19px;"
Address
/td
td style="width: 100px; height: 19px;"
Country
/td
/tr
tr
td style="width: 100px"
asp:TextBox ID="TextBox1" runat="server"/
/td
td style="width: 100px"
asp:TextBox ID="TextBox2" runat="server"/
/td
td style="width: 100px"
td style="width: 100px"
asp:TextBox ID="TextBox4" runat="server"/
/td>
td style="width: 100px"
asp:TextBox ID="TextBox5" runat="server"/
/td
td style="width: 100px"
asp:TextBox ID="TextBox6" runat="server"/
/td
td style="width: 100px"
asp:Button ID="Button1" runat="server" Text="Add New" OnClick="Button1_Click" /
/td
/tr
/table
asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" ShowFooter="true"
Columns
asp:BoundField DataField="CustomerID" HeaderText="ID" ReadOnly="true"/
asp:BoundField DataField="CompanyName" HeaderText="Company"/
asp:BoundField DataField="ContactName" HeaderText="Name"/
asp:BoundField DataField="ContactTitle" HeaderText="Title" /
asp:BoundField DataField="Address" HeaderText="Address"/
asp:BoundField DataField="Country" HeaderText="Country"/
/Columns
/asp:GridView
/div
/form
/body
/html
Note: I have set the CustomerID field to ReadOnly so that the field cannot be edited.
STEP 4: Binding GridView with Data
I will not elaborate on this step because I already describe the details in my previous example about “Binding GridView with Data”. Here are the code blocks for binding the GridView.
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGridView();
}
}
private string GetConnectionString()
{
return System.Configuration.ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
}
#region Bind GridView
private void BindGridView()
{
DataTable dt = new DataTable();
SqlConnection connection = new SqlConnection(GetConnectionString());
try
{
connection.Open();
string sqlStatement = "SELECT Top(10)* FROM Customers";
SqlCommand cmd = new SqlCommand(sqlStatement, connection);
SqlDataAdapter sqlDa = new SqlDataAdapter(cmd);
sqlDa.Fill(dt);
if (dt.Rows.Count > 0)
{
GridView1.DataSource = dt;
GridView1.DataBind();
}
}
catch (System.Data.SqlClient.SqlException ex)
{
string msg = "Fetch Error:";
msg += ex.Message;
throw new Exception(msg);
}
finally
{
connection.Close();
}
}
#endregion
}
Now, we already know how to bind our GridView with data from database. So let’s proceed on adding a new data in GridView.
STEP 5: Adding New Data in GridView
As you have noticed in STEP 2, we have added six TextBox and a Button in the web form in order for us to type the information there and Insert them to the database. Now let’s create a method for executing the Update or Insert.
Here are the code blocks for our Insert and Update method in the code behind:
#region Insert New or Update Record
private void UpdateOrAddNewRecord(string ID, string Company, string Name, string Title, string Address, string Country, bool isUpdate)
{
SqlConnection connection = new SqlConnection(GetConnectionString());
string sqlStatement = string.Empty;
if (!isUpdate)
{
sqlStatement = "INSERT INTO Customers"+
"(CustomerID,CompanyName,ContactName,ContactTitle,Address,Country)" +
"VALUES (@CustomerID,@CompanyName,@ContactName,@ContactTitle,@Address,@Country)";
}
else
{
sqlStatement = "UPDATE Customers" +
"SET CompanyName = @CompanyName,
ContactName = @ContactName," +
"ContactTitle = @ContactTitle,Address =
@Address,Country = @Country" +
"WHERE CustomerID = @CustomerID,";
}
try
{
connection.Open();
SqlCommand cmd = new SqlCommand(sqlStatement, connection);
cmd.Parameters.AddWithValue("@CustomerID", ID);
cmd.Parameters.AddWithValue("@CompanyName", Company);
cmd.Parameters.AddWithValue("@ContactName", Name);
cmd.Parameters.AddWithValue("@ContactTitle", Title);
cmd.Parameters.AddWithValue("@Address", Address);
cmd.Parameters.AddWithValue("@Country", Country);
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
catch (System.Data.SqlClient.SqlException ex)
{
string msg = "Insert/Update Error:";
msg += ex.Message;
throw new Exception(msg);
}
finally
{
connection.Close();
}
}
#endregion
The UpdateOrAddNewRecord is a method that takes seven parameters. Six of those paramaters basically comes from the TextBox values that were entered in the page. The last parameter is a boolean value which tells the method whether to execute an Insert (false) or Update (true). Default is true.
Here’s the code block for calling the method UpdateOrAddNewRecord on Button_Click event and pass the corresponding parameters needed:
protected void Button1_Click(object sender, EventArgs e)
{
UpdateOrAddNewRecord(TextBox1.Text, TextBox2.Text, TextBox3.Text, TextBox4.Text, TextBox5.Text, TextBox6.Text, false);
//Re Bind GridView to reflect changes made
BindGridView();
}
As you can see from above, We have called the BindGridView() method again in order to reflect the changes made and display the new added data in the GridView. See output below with red mark.
STEP 6: Edit and Update Records In GridView
One of the good things about GridView is that it provides a built-in CommandField Buttons which allows us to perform certain actions like editing, updating,deleting and selecting of GridView data.
To add those command fields mentioned in the GridView you can follow these few steps below:
1. Switch to Design View
2. Right Click on the GridView and Select --> Show Smart Tag --> Add New Columns
3. On the List Select CommandField
4. Check Delete and Edit/Update options then OK
As you can see the Edit and Delete CommandField are automatically added in the last column of GridView. Now we can start to write our codes for editing and updating the information in the GridView.
In-order to perform Edit and Update in GridView we need to use three events ( GridView_RowEditing, GridView_RowCancelingEdit , GridView_RowUpdating). For those who do not know on how to generate Events in GridView you can follow these steps below:
1. Switch to Design View in Visual Studio Designer
2. Click on the GridView
3. Navigate to the GridView Property Pane and then SWITCH to Event Properties
4. From there you would be able to find the list of events including those three events mentioned above
5. Double Click on that to generate the Event handler for you
6. Then write the codes there
Here’s the code for each events:
protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
GridView1.EditIndex = e.NewEditIndex; // turn to edit mode
BindGridView(); // Rebind GridView to show the data in edit mode
}
protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
GridView1.EditIndex = -1; //swicth back to default mode
BindGridView(); // Rebind GridView to show the data in default mode
}
protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
//Accessing Edited values from the GridView
string id = GridView1.Rows[e.RowIndex].Cells[0].Text; //ID
string company = ((TextBox)GridView1.Rows[e.RowIndex].Cells[1].Controls[0]).Text; //Company
string name = ((TextBox)GridView1.Rows[e.RowIndex].Cells[2].Controls[0]).Text; //Name
string title = ((TextBox)GridView1.Rows[e.RowIndex].Cells[3].Controls[0]).Text; //Title
string address = ((TextBox)GridView1.Rows[e.RowIndex].Cells[4].Controls[0]).Text; //Address
string country = ((TextBox)GridView1.Rows[e.RowIndex].Cells[5].Controls[0]).Text; //Country
UpdateOrAddNewRecord(id,company,name,title,address,country,true); // call update method
GridView1.EditIndex = -1;
BindGridView(); // Rebind GridView to reflect changes made
}
STEP 7: Perform Delete in GridView
Since we are using the Built-in Delete CommandField Button in GridView, we can use the GridView_RowDeleting event to delete specific row in GridView.
Here’s the code block for the Delete method:
#region Delete Record
private void DeleteRecord(string ID)
{
SqlConnection connection = new SqlConnection(GetConnectionString());
string sqlStatement = "DELETE FROM Customers WHERE CustomerID = @CustomerID";
try
{
connection.Open();
SqlCommand cmd = new SqlCommand(sqlStatement, connection);
cmd.Parameters.AddWithValue("@CustomerID", ID);
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
catch (System.Data.SqlClient.SqlException ex)
{
string msg = "Deletion Error:";
msg += ex.Message;
throw new Exception(msg);
}
finally
{
connection.Close();
}
}
#endregion
Here’s the code block for calling the delete method at RowDeleting event
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
string id = GridView1.Rows[e.RowIndex].Cells[0].Text; get the id of the selected row
DeleteRecord(id);//call delete method
BindGridView();//rebind grid to reflect changes made
}
That’s it!
Note: If you wan’t to display a confirmation when deleting a row in GridView then you can refer to my next example about “Display Confirmation Message on GridView Deleting”
GridView Insert, Edit, Update and Delete – The Ado.Net way
This demo is a continuation of my previous example “Binding GridView with Data”. If you are not familiar of binding the GridView the ado.net way then I would suggest looking at my previous example first before you proceed to this example.
Basically, this demo describes the basic way to do INSERT, EDIT, UPDATE and DELETE data in ASPNET GridView Control using the ADO.NET way.
STEP 1: Creating a DataBase Table
In this demo, I presumed that you already have a basic background on how to create a simple database table. In this example, this time I used my own database called SampleDB which has Customers Table and basically contains the following field columns:
CustomerID – PK
CompanyName
ContactName
ContactTitle
Address
Country
STEP 2: Setting Up the Connection String
connectionStrings
add name="DBConnection" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\SampleDB.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/
/connectionStrings
STEP 3: Setting up the GUI
Just for the simplicity of this demo, I set up the GUI like this:
html xmlns="http://www.w3.org/1999/xhtml"
head runat="server"
title
GridView Data Manipulation
/title
/head
body
form id="form1" runat="server"
div
table cellpadding="0" cellspacing="0"
tr
td style="width: 100px; height: 19px;"
Company ID
/td
td style="width: 100px; height: 19px;"
Company
/td>
td style="width: 100px; height: 19px;"
Name
/td
td style="width: 100px; height: 19px;"
Title
/td
td style="width: 100px; height: 19px;"
Address
/td
td style="width: 100px; height: 19px;"
Country
/td
/tr
tr
td style="width: 100px"
asp:TextBox ID="TextBox1" runat="server"/
/td
td style="width: 100px"
asp:TextBox ID="TextBox2" runat="server"/
/td
td style="width: 100px"
td style="width: 100px"
asp:TextBox ID="TextBox4" runat="server"/
/td>
td style="width: 100px"
asp:TextBox ID="TextBox5" runat="server"/
/td
td style="width: 100px"
asp:TextBox ID="TextBox6" runat="server"/
/td
td style="width: 100px"
asp:Button ID="Button1" runat="server" Text="Add New" OnClick="Button1_Click" /
/td
/tr
/table
asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" ShowFooter="true"
Columns
asp:BoundField DataField="CustomerID" HeaderText="ID" ReadOnly="true"/
asp:BoundField DataField="CompanyName" HeaderText="Company"/
asp:BoundField DataField="ContactName" HeaderText="Name"/
asp:BoundField DataField="ContactTitle" HeaderText="Title" /
asp:BoundField DataField="Address" HeaderText="Address"/
asp:BoundField DataField="Country" HeaderText="Country"/
/Columns
/asp:GridView
/div
/form
/body
/html
Note: I have set the CustomerID field to ReadOnly so that the field cannot be edited.
STEP 4: Binding GridView with Data
I will not elaborate on this step because I already describe the details in my previous example about “Binding GridView with Data”. Here are the code blocks for binding the GridView.
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGridView();
}
}
private string GetConnectionString()
{
return System.Configuration.ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
}
#region Bind GridView
private void BindGridView()
{
DataTable dt = new DataTable();
SqlConnection connection = new SqlConnection(GetConnectionString());
try
{
connection.Open();
string sqlStatement = "SELECT Top(10)* FROM Customers";
SqlCommand cmd = new SqlCommand(sqlStatement, connection);
SqlDataAdapter sqlDa = new SqlDataAdapter(cmd);
sqlDa.Fill(dt);
if (dt.Rows.Count > 0)
{
GridView1.DataSource = dt;
GridView1.DataBind();
}
}
catch (System.Data.SqlClient.SqlException ex)
{
string msg = "Fetch Error:";
msg += ex.Message;
throw new Exception(msg);
}
finally
{
connection.Close();
}
}
#endregion
}
Now, we already know how to bind our GridView with data from database. So let’s proceed on adding a new data in GridView.
STEP 5: Adding New Data in GridView
As you have noticed in STEP 2, we have added six TextBox and a Button in the web form in order for us to type the information there and Insert them to the database. Now let’s create a method for executing the Update or Insert.
Here are the code blocks for our Insert and Update method in the code behind:
#region Insert New or Update Record
private void UpdateOrAddNewRecord(string ID, string Company, string Name, string Title, string Address, string Country, bool isUpdate)
{
SqlConnection connection = new SqlConnection(GetConnectionString());
string sqlStatement = string.Empty;
if (!isUpdate)
{
sqlStatement = "INSERT INTO Customers"+
"(CustomerID,CompanyName,ContactName,ContactTitle,Address,Country)" +
"VALUES (@CustomerID,@CompanyName,@ContactName,@ContactTitle,@Address,@Country)";
}
else
{
sqlStatement = "UPDATE Customers" +
"SET CompanyName = @CompanyName,
ContactName = @ContactName," +
"ContactTitle = @ContactTitle,Address =
@Address,Country = @Country" +
"WHERE CustomerID = @CustomerID,";
}
try
{
connection.Open();
SqlCommand cmd = new SqlCommand(sqlStatement, connection);
cmd.Parameters.AddWithValue("@CustomerID", ID);
cmd.Parameters.AddWithValue("@CompanyName", Company);
cmd.Parameters.AddWithValue("@ContactName", Name);
cmd.Parameters.AddWithValue("@ContactTitle", Title);
cmd.Parameters.AddWithValue("@Address", Address);
cmd.Parameters.AddWithValue("@Country", Country);
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
catch (System.Data.SqlClient.SqlException ex)
{
string msg = "Insert/Update Error:";
msg += ex.Message;
throw new Exception(msg);
}
finally
{
connection.Close();
}
}
#endregion
The UpdateOrAddNewRecord is a method that takes seven parameters. Six of those paramaters basically comes from the TextBox values that were entered in the page. The last parameter is a boolean value which tells the method whether to execute an Insert (false) or Update (true). Default is true.
Here’s the code block for calling the method UpdateOrAddNewRecord on Button_Click event and pass the corresponding parameters needed:
protected void Button1_Click(object sender, EventArgs e)
{
UpdateOrAddNewRecord(TextBox1.Text, TextBox2.Text, TextBox3.Text, TextBox4.Text, TextBox5.Text, TextBox6.Text, false);
//Re Bind GridView to reflect changes made
BindGridView();
}
As you can see from above, We have called the BindGridView() method again in order to reflect the changes made and display the new added data in the GridView. See output below with red mark.
STEP 6: Edit and Update Records In GridView
One of the good things about GridView is that it provides a built-in CommandField Buttons which allows us to perform certain actions like editing, updating,deleting and selecting of GridView data.
To add those command fields mentioned in the GridView you can follow these few steps below:
1. Switch to Design View
2. Right Click on the GridView and Select --> Show Smart Tag --> Add New Columns
3. On the List Select CommandField
4. Check Delete and Edit/Update options then OK
As you can see the Edit and Delete CommandField are automatically added in the last column of GridView. Now we can start to write our codes for editing and updating the information in the GridView.
In-order to perform Edit and Update in GridView we need to use three events ( GridView_RowEditing, GridView_RowCancelingEdit , GridView_RowUpdating). For those who do not know on how to generate Events in GridView you can follow these steps below:
1. Switch to Design View in Visual Studio Designer
2. Click on the GridView
3. Navigate to the GridView Property Pane and then SWITCH to Event Properties
4. From there you would be able to find the list of events including those three events mentioned above
5. Double Click on that to generate the Event handler for you
6. Then write the codes there
Here’s the code for each events:
protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
GridView1.EditIndex = e.NewEditIndex; // turn to edit mode
BindGridView(); // Rebind GridView to show the data in edit mode
}
protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
GridView1.EditIndex = -1; //swicth back to default mode
BindGridView(); // Rebind GridView to show the data in default mode
}
protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
//Accessing Edited values from the GridView
string id = GridView1.Rows[e.RowIndex].Cells[0].Text; //ID
string company = ((TextBox)GridView1.Rows[e.RowIndex].Cells[1].Controls[0]).Text; //Company
string name = ((TextBox)GridView1.Rows[e.RowIndex].Cells[2].Controls[0]).Text; //Name
string title = ((TextBox)GridView1.Rows[e.RowIndex].Cells[3].Controls[0]).Text; //Title
string address = ((TextBox)GridView1.Rows[e.RowIndex].Cells[4].Controls[0]).Text; //Address
string country = ((TextBox)GridView1.Rows[e.RowIndex].Cells[5].Controls[0]).Text; //Country
UpdateOrAddNewRecord(id,company,name,title,address,country,true); // call update method
GridView1.EditIndex = -1;
BindGridView(); // Rebind GridView to reflect changes made
}
STEP 7: Perform Delete in GridView
Since we are using the Built-in Delete CommandField Button in GridView, we can use the GridView_RowDeleting event to delete specific row in GridView.
Here’s the code block for the Delete method:
#region Delete Record
private void DeleteRecord(string ID)
{
SqlConnection connection = new SqlConnection(GetConnectionString());
string sqlStatement = "DELETE FROM Customers WHERE CustomerID = @CustomerID";
try
{
connection.Open();
SqlCommand cmd = new SqlCommand(sqlStatement, connection);
cmd.Parameters.AddWithValue("@CustomerID", ID);
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
catch (System.Data.SqlClient.SqlException ex)
{
string msg = "Deletion Error:";
msg += ex.Message;
throw new Exception(msg);
}
finally
{
connection.Close();
}
}
#endregion
Here’s the code block for calling the delete method at RowDeleting event
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
string id = GridView1.Rows[e.RowIndex].Cells[0].Text; get the id of the selected row
DeleteRecord(id);//call delete method
BindGridView();//rebind grid to reflect changes made
}
That’s it!
Note: If you wan’t to display a confirmation when deleting a row in GridView then you can refer to my next example about “Display Confirmation Message on GridView Deleting”
gridview row can use check box to download file and display excel format
1 . script language="javascript" type="text/javascript"
var AFID = "";
function GetID(rowID, AudioFileID) {
AFID = AudioFileID;
ChangeColor(rowID, 'gvAudioFiles');
}
function ChangeRowColorForMouseMove(rowID) {
ChangeColor1(rowID, 'gvAudioFiles');
}
/script
2. script type="text/javascript" language="javascript"
function SelectAll(CheckBox)
{
TotalChkBx = parseInt('<%= this.gvAudioFiles.Rows.Count %>');
var TargetBaseControl = document.getElementById('<%= this.gvAudioFiles.ClientID %>');
var TargetChildControl = "chkSelect";
var Inputs = TargetBaseControl.getElementsByTagName("input");
for(var iCount = 0; iCount < type ="=">= 0)
Inputs[iCount].checked = CheckBox.checked;
}
}
function SelectDeSelectHeader(CheckBox)
{
TotalChkBx = parseInt('<%= this.gvAudioFiles.Rows.Count %>');
var TargetBaseControl = document.getElementById('<%= this.gvAudioFiles.ClientID %>');
var TargetChildControl = "chkSelect";
var TargetHeaderControl = "chkSelectAll";
var Inputs = TargetBaseControl.getElementsByTagName("input");
var flag = false;
var HeaderCheckBox;
for(var iCount = 0; iCount < type ="=">= 0)
HeaderCheckBox = Inputs[iCount];
if(Inputs[iCount] != CheckBox && Inputs[iCount].type == 'checkbox' && Inputs[iCount].id.indexOf(TargetChildControl,0) >= 0 && Inputs[iCount].id.indexOf(TargetHeaderControl,0) == -1)
{
if(CheckBox.checked)
{
if(!Inputs[iCount].checked)
{
flag = false;
HeaderCheckBox.checked = false;
return;
}
else
flag = true;
}
else if(!CheckBox.checked)
HeaderCheckBox.checked = false;
}
}
if(flag)
HeaderCheckBox.checked = CheckBox.checked
}
/script
3. script type="text/javascript"
/*Check box validation in gridview*/
var TargetBaseControl = null;
window.onload = function()
{
try
{
//get target base control.
TargetBaseControl =
document.getElementById('<%= this.gvAudioFiles.ClientID %>');
}
catch(err)
{
TargetBaseControl = null;
}
}
function TestCheckBox()
{
if(TargetBaseControl == null) return false;
//get target child control.
var TargetChildControl = "chkSelect";
//get all the control of the type INPUT in the base control.
var Inputs = TargetBaseControl.getElementsByTagName("input");
for(var n = 0; n < type ="=">= 0 &&
Inputs[n].checked)
return true;
alert('Select at least one audio file!');
return false;
}
/script
4. Gridview
asp:GridView ID="gvAudioFiles" runat="server" AutoGenerateColumns="false" OnRowDataBound="gvAudioFiles_RowDataBound" Width="100%" AllowPaging="true" AllowSorting="true" OnPageIndexChanging="gvAudioFiles_PageIndexChanging1" OnSorting="gvAudioFiles_Sorting">
Columns
asp:TemplateField HeaderText=""
HeaderTemplate
asp:CheckBox ID="chkSelectAll" runat="server"onclick="SelectAll(this);" />
/HeaderTemplate
ItemTemplate
asp:CheckBox ID="chkSelect" runat="server" /
/ItemTemplate
/asp:TemplateField
asp:BoundField DataField="DoctorName" HeaderText="Doctor Name" HeaderStyle-CssClass="submenu"
SortExpression="DoctorName" /
asp:TemplateField HeaderText="Audio File" HeaderStyle-CssClass="submenu"
ItemTemplate
asp:HyperLink ID="hlnkAudio" runat="server" Target="_blank" Text='<%#Eval("AudioFileName") %>'
ToolTip="Click here to download" NavigateUrl='<%#string.Concat("~/AudioFiles/",DataBinder.Eval(Container.DataItem, "UserName"),"/",Convert.ToDateTime(Eval("FileDate")).ToLongDateString(),"//", DataBinder.Eval(Container.DataItem, "AudioFileName")) %>'
/asp:HyperLink
asp:Label ID="lblAudioFileID" runat="server" Text='<%#Eval("AudioFileID") %>' Visible="false" /
/ItemTemplate
/asp:TemplateField
asp:TemplateField HeaderText="File Added on" HeaderStyle-CssClass="submenu" SortExpression="FileDate"
ItemTemplate
asp:Label ID="lblAudioFileDate" runat="server" Text='<%# Convert.ToDateTime(Eval("FileDate")).ToShortDateString()%>' /
/ItemTemplate
/asp:TemplateField
/Columns
/asp:GridView
5. Buttons
asp:Button ID="btnAssign" runat="server" Text="Assign" CssClass="submitbutton" OnClick="btnAssign_Click"
PostBackUrl="AssignAudioFilesToQA.aspx" /
asp:Button ID="btnFileIDs" runat="server" Visible="false"
/asp:Button
asp:Button ID="btnDownload" runat="server" Text="Download" OnClick="btnDownload_Click" /
asp:Button ID="Button1" runat="server" Text="Export to Excel" OnClick="Button1_Click" /
6 c#
in project add bin this file
ICSharpCode.SharpZipLib.dll
7. code c#
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using StreamlineMT.BO;
using StreamlineMT.BLL;
using ICSharpCode.SharpZipLib.Checksums;
using ICSharpCode.SharpZipLib.Zip;
using ICSharpCode.SharpZipLib.GZip;
using System.IO;
public partial class test : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
VerifyRenderingInServerForm(form1);
Response.Cache.SetCacheability(HttpCacheability.NoCache);
btnAssign.Attributes.Add("onclick", "if (!TestCheckBox()) { return false; }");
if (!Page.IsPostBack)
{
ViewState["sortOrder"] = "";
bindGridView("", "");
}
// GetAudioFiles();
}
private void bindGridView(string sortExp, string sortDir)
{
AudioFileBLL objBLL = new AudioFileBLL();
AudioFileBO objBO = new AudioFileBO();
DataSet ds = new DataSet();
objBO.Type = "SearchFiles";
objBO.FromDate = "12/01/2009";
objBO.ToDate = "12/04/2009";
ds = objBLL.GetAudioFiles(objBO);
if (ds.Tables[0].Rows.Count == 0)
btnAssign.Visible = false;
else
{
DataView myDataView = new DataView();
myDataView = ds.Tables[0].DefaultView;
if (sortExp != string.Empty)
{
myDataView.Sort = string.Format("{0} {1}", sortExp, sortDir);
}
btnAssign.Visible = true;
myDataView.RowFilter = "(FileDate >='12/04/2009 00:00:01') and (FileDate<='12/04/2009 23:59:59')"; gvAudioFiles.DataSource = myDataView; gvAudioFiles.DataBind(); } } protected void gvAudioFiles_RowDataBound(object sender, GridViewRowEventArgs e) { if (e.Row.RowType == DataControlRowType.DataRow) { //Label lblStatus = (Label)e.Row.FindControl("lblStatus"); //HyperLink hlnkAssign = (HyperLink)e.Row.FindControl("hlnkAssign"); //if (lblStatus.Text == "") // hlnkAssign.Visible = true; //else // hlnkAssign.Visible = false; //if (lblStatus.Text == "8") // e.Row.Cells[5].Style.Add("background-color", "#ffa463"); string rowID = String.Empty; if (e.Row.RowType == DataControlRowType.DataRow) { rowID = "row" + e.Row.RowIndex; e.Row.Attributes.Add("Id", rowID); // e.Row.Attributes.Add("onclick", "javascript:GetID('" + rowID + "','" + DataBinder.Eval(e.Row.DataItem, "TemplateID") + "');"); e.Row.Attributes.Add("onmouseover", "javascript:ChangeRowColorForMouseMove('" + rowID + "');"); } } //Select All if (e.Row.RowType == DataControlRowType.Header) { //adding an attribute for onclick event on the check box in the header //and passing the ClientID of the Select All checkbox //((CheckBox)e.Row.FindControl("cbSelectAll")).Attributes.Add("onclick", "javascript:SelectAll('" + ((CheckBox)e.Row.FindControl("cbSelectAll")).ClientID + "')"); } try { if (e.Row.RowType == DataControlRowType.DataRow) { string strScript = "SelectDeSelectHeader(" + ((CheckBox)e.Row.Cells[0].FindControl("chkSelect")).ClientID + ");"; ((CheckBox)e.Row.Cells[0].FindControl("chkSelect")).Attributes.Add("onclick", strScript); } } catch (Exception Ex) { //report error } } protected void btnAssign_Click(object sender, EventArgs e) { string fileIDs = ""; foreach (GridViewRow gr in gvAudioFiles.Rows) { CheckBox chkSelect = (CheckBox)gr.FindControl("chkSelect"); if (chkSelect.Checked) { Label lblAudioFileID = (Label)gr.FindControl("lblAudioFileID"); fileIDs = fileIDs + lblAudioFileID.Text + ","; //SqlParameter S1=new SqlParameter( } } if (fileIDs != "") { btnFileIDs.Text = fileIDs.TrimEnd(','); btnAssign.PostBackUrl = "AssignAudioFilesToQA.aspx?stat=audi"; //Response.Redirect("AssignAudioFilesToQA.aspx?stat=audi"); } else { string popupScript = "";
ScriptManager.RegisterStartupScript(this, this.GetType(), Guid.NewGuid().ToString(), popupScript, false);
}
}
protected void gvAudioFiles_PageIndexChanging1(object sender, GridViewPageEventArgs e)
{
gvAudioFiles.PageIndex = e.NewPageIndex;
bindGridView("", "");
}
protected void gvAudioFiles_Sorting(object sender, GridViewSortEventArgs e)
{
bindGridView(e.SortExpression, sortOrder);
}
public string sortOrder
{
get
{
if (ViewState["sortOrder"].ToString() == "desc")
ViewState["sortOrder"] = "asc";
else
ViewState["sortOrder"] = "desc";
return ViewState["sortOrder"].ToString();
}
set
{
ViewState["sortOrder"] = value;
}
}
protected void btnDownload_Click(object sender, EventArgs e)
{
ArrayList filelist = new ArrayList();
string fileLoc = "";
foreach (GridViewRow gr in gvAudioFiles.Rows)
{
CheckBox chkSelect = (CheckBox)gr.FindControl("chkSelect");
if (chkSelect.Checked)
{
HyperLink hlnkAudio = (HyperLink)gr.FindControl("hlnkAudio");
fileLoc = fileLoc + hlnkAudio.NavigateUrl + ",";
filelist.Add(hlnkAudio.NavigateUrl);
//SqlParameter S1=new SqlParameter(
}
}
if (filelist.Count != 0)
{
fileLoc = fileLoc.TrimEnd(',');
string zipFullPath = Server.MapPath("~\\") + "DocumentFolder.zip";
ZipOutputStream zipOut = new ZipOutputStream(File.Create(zipFullPath));
//string[] f = { "D:\\Projects\\STREAMLINEMT\\StreamMT\\AudioFiles\\linder\\Friday, November 27, 2009\\162-13742409.VW", "D:\\Projects\\STREAMLINEMT\\StreamMT\\AudioFiles\\linder\\Friday, November 27, 2009\\13742409.Wav.wav" };
for (int i = 0; i < fi =" new" entry =" new" sreader =" File.OpenRead(Server.MapPath(filelist[i].ToString()));" buff =" new" datetime =" fi.LastWriteTime;" size =" sReader.Length;" postbackurl = "AssignAudioFilesToQA.aspx?stat=audi" stat="audi" targetf =" new" contenttype = "application/zip" filename=" + targetf.Name); FileStream sourceFile = new FileStream(@fapth, FileMode.Open); long FileSize; FileSize = sourceFile.Length; byte[] getContent = new byte[(int)FileSize]; sourceFile.Read(getContent, 0, (int)sourceFile.Length); sourceFile.Close(); Response.BinaryWrite(getContent); } private void exporttoGrid() { Response.Clear(); Response.AddHeader(" filename="FileName.xls" charset = "" contenttype = "application/vnd.xls" stringwrite =" new" htmlwrite =" new" ld =" GetDSSDuration(" popupscript = "
var AFID = "";
function GetID(rowID, AudioFileID) {
AFID = AudioFileID;
ChangeColor(rowID, 'gvAudioFiles');
}
function ChangeRowColorForMouseMove(rowID) {
ChangeColor1(rowID, 'gvAudioFiles');
}
/script
2. script type="text/javascript" language="javascript"
function SelectAll(CheckBox)
{
TotalChkBx = parseInt('<%= this.gvAudioFiles.Rows.Count %>');
var TargetBaseControl = document.getElementById('<%= this.gvAudioFiles.ClientID %>');
var TargetChildControl = "chkSelect";
var Inputs = TargetBaseControl.getElementsByTagName("input");
for(var iCount = 0; iCount < type ="=">= 0)
Inputs[iCount].checked = CheckBox.checked;
}
}
function SelectDeSelectHeader(CheckBox)
{
TotalChkBx = parseInt('<%= this.gvAudioFiles.Rows.Count %>');
var TargetBaseControl = document.getElementById('<%= this.gvAudioFiles.ClientID %>');
var TargetChildControl = "chkSelect";
var TargetHeaderControl = "chkSelectAll";
var Inputs = TargetBaseControl.getElementsByTagName("input");
var flag = false;
var HeaderCheckBox;
for(var iCount = 0; iCount < type ="=">= 0)
HeaderCheckBox = Inputs[iCount];
if(Inputs[iCount] != CheckBox && Inputs[iCount].type == 'checkbox' && Inputs[iCount].id.indexOf(TargetChildControl,0) >= 0 && Inputs[iCount].id.indexOf(TargetHeaderControl,0) == -1)
{
if(CheckBox.checked)
{
if(!Inputs[iCount].checked)
{
flag = false;
HeaderCheckBox.checked = false;
return;
}
else
flag = true;
}
else if(!CheckBox.checked)
HeaderCheckBox.checked = false;
}
}
if(flag)
HeaderCheckBox.checked = CheckBox.checked
}
/script
3. script type="text/javascript"
/*Check box validation in gridview*/
var TargetBaseControl = null;
window.onload = function()
{
try
{
//get target base control.
TargetBaseControl =
document.getElementById('<%= this.gvAudioFiles.ClientID %>');
}
catch(err)
{
TargetBaseControl = null;
}
}
function TestCheckBox()
{
if(TargetBaseControl == null) return false;
//get target child control.
var TargetChildControl = "chkSelect";
//get all the control of the type INPUT in the base control.
var Inputs = TargetBaseControl.getElementsByTagName("input");
for(var n = 0; n < type ="=">= 0 &&
Inputs[n].checked)
return true;
alert('Select at least one audio file!');
return false;
}
/script
4. Gridview
asp:GridView ID="gvAudioFiles" runat="server" AutoGenerateColumns="false" OnRowDataBound="gvAudioFiles_RowDataBound" Width="100%" AllowPaging="true" AllowSorting="true" OnPageIndexChanging="gvAudioFiles_PageIndexChanging1" OnSorting="gvAudioFiles_Sorting">
Columns
asp:TemplateField HeaderText=""
HeaderTemplate
asp:CheckBox ID="chkSelectAll" runat="server"onclick="SelectAll(this);" />
/HeaderTemplate
ItemTemplate
asp:CheckBox ID="chkSelect" runat="server" /
/ItemTemplate
/asp:TemplateField
asp:BoundField DataField="DoctorName" HeaderText="Doctor Name" HeaderStyle-CssClass="submenu"
SortExpression="DoctorName" /
asp:TemplateField HeaderText="Audio File" HeaderStyle-CssClass="submenu"
ItemTemplate
asp:HyperLink ID="hlnkAudio" runat="server" Target="_blank" Text='<%#Eval("AudioFileName") %>'
ToolTip="Click here to download" NavigateUrl='<%#string.Concat("~/AudioFiles/",DataBinder.Eval(Container.DataItem, "UserName"),"/",Convert.ToDateTime(Eval("FileDate")).ToLongDateString(),"//", DataBinder.Eval(Container.DataItem, "AudioFileName")) %>'
/asp:HyperLink
asp:Label ID="lblAudioFileID" runat="server" Text='<%#Eval("AudioFileID") %>' Visible="false" /
/ItemTemplate
/asp:TemplateField
asp:TemplateField HeaderText="File Added on" HeaderStyle-CssClass="submenu" SortExpression="FileDate"
ItemTemplate
asp:Label ID="lblAudioFileDate" runat="server" Text='<%# Convert.ToDateTime(Eval("FileDate")).ToShortDateString()%>' /
/ItemTemplate
/asp:TemplateField
/Columns
/asp:GridView
5. Buttons
asp:Button ID="btnAssign" runat="server" Text="Assign" CssClass="submitbutton" OnClick="btnAssign_Click"
PostBackUrl="AssignAudioFilesToQA.aspx" /
asp:Button ID="btnFileIDs" runat="server" Visible="false"
/asp:Button
asp:Button ID="btnDownload" runat="server" Text="Download" OnClick="btnDownload_Click" /
asp:Button ID="Button1" runat="server" Text="Export to Excel" OnClick="Button1_Click" /
6 c#
in project add bin this file
ICSharpCode.SharpZipLib.dll
7. code c#
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using StreamlineMT.BO;
using StreamlineMT.BLL;
using ICSharpCode.SharpZipLib.Checksums;
using ICSharpCode.SharpZipLib.Zip;
using ICSharpCode.SharpZipLib.GZip;
using System.IO;
public partial class test : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
VerifyRenderingInServerForm(form1);
Response.Cache.SetCacheability(HttpCacheability.NoCache);
btnAssign.Attributes.Add("onclick", "if (!TestCheckBox()) { return false; }");
if (!Page.IsPostBack)
{
ViewState["sortOrder"] = "";
bindGridView("", "");
}
// GetAudioFiles();
}
private void bindGridView(string sortExp, string sortDir)
{
AudioFileBLL objBLL = new AudioFileBLL();
AudioFileBO objBO = new AudioFileBO();
DataSet ds = new DataSet();
objBO.Type = "SearchFiles";
objBO.FromDate = "12/01/2009";
objBO.ToDate = "12/04/2009";
ds = objBLL.GetAudioFiles(objBO);
if (ds.Tables[0].Rows.Count == 0)
btnAssign.Visible = false;
else
{
DataView myDataView = new DataView();
myDataView = ds.Tables[0].DefaultView;
if (sortExp != string.Empty)
{
myDataView.Sort = string.Format("{0} {1}", sortExp, sortDir);
}
btnAssign.Visible = true;
myDataView.RowFilter = "(FileDate >='12/04/2009 00:00:01') and (FileDate<='12/04/2009 23:59:59')"; gvAudioFiles.DataSource = myDataView; gvAudioFiles.DataBind(); } } protected void gvAudioFiles_RowDataBound(object sender, GridViewRowEventArgs e) { if (e.Row.RowType == DataControlRowType.DataRow) { //Label lblStatus = (Label)e.Row.FindControl("lblStatus"); //HyperLink hlnkAssign = (HyperLink)e.Row.FindControl("hlnkAssign"); //if (lblStatus.Text == "") // hlnkAssign.Visible = true; //else // hlnkAssign.Visible = false; //if (lblStatus.Text == "8") // e.Row.Cells[5].Style.Add("background-color", "#ffa463"); string rowID = String.Empty; if (e.Row.RowType == DataControlRowType.DataRow) { rowID = "row" + e.Row.RowIndex; e.Row.Attributes.Add("Id", rowID); // e.Row.Attributes.Add("onclick", "javascript:GetID('" + rowID + "','" + DataBinder.Eval(e.Row.DataItem, "TemplateID") + "');"); e.Row.Attributes.Add("onmouseover", "javascript:ChangeRowColorForMouseMove('" + rowID + "');"); } } //Select All if (e.Row.RowType == DataControlRowType.Header) { //adding an attribute for onclick event on the check box in the header //and passing the ClientID of the Select All checkbox //((CheckBox)e.Row.FindControl("cbSelectAll")).Attributes.Add("onclick", "javascript:SelectAll('" + ((CheckBox)e.Row.FindControl("cbSelectAll")).ClientID + "')"); } try { if (e.Row.RowType == DataControlRowType.DataRow) { string strScript = "SelectDeSelectHeader(" + ((CheckBox)e.Row.Cells[0].FindControl("chkSelect")).ClientID + ");"; ((CheckBox)e.Row.Cells[0].FindControl("chkSelect")).Attributes.Add("onclick", strScript); } } catch (Exception Ex) { //report error } } protected void btnAssign_Click(object sender, EventArgs e) { string fileIDs = ""; foreach (GridViewRow gr in gvAudioFiles.Rows) { CheckBox chkSelect = (CheckBox)gr.FindControl("chkSelect"); if (chkSelect.Checked) { Label lblAudioFileID = (Label)gr.FindControl("lblAudioFileID"); fileIDs = fileIDs + lblAudioFileID.Text + ","; //SqlParameter S1=new SqlParameter( } } if (fileIDs != "") { btnFileIDs.Text = fileIDs.TrimEnd(','); btnAssign.PostBackUrl = "AssignAudioFilesToQA.aspx?stat=audi"; //Response.Redirect("AssignAudioFilesToQA.aspx?stat=audi"); } else { string popupScript = "";
ScriptManager.RegisterStartupScript(this, this.GetType(), Guid.NewGuid().ToString(), popupScript, false);
}
}
protected void gvAudioFiles_PageIndexChanging1(object sender, GridViewPageEventArgs e)
{
gvAudioFiles.PageIndex = e.NewPageIndex;
bindGridView("", "");
}
protected void gvAudioFiles_Sorting(object sender, GridViewSortEventArgs e)
{
bindGridView(e.SortExpression, sortOrder);
}
public string sortOrder
{
get
{
if (ViewState["sortOrder"].ToString() == "desc")
ViewState["sortOrder"] = "asc";
else
ViewState["sortOrder"] = "desc";
return ViewState["sortOrder"].ToString();
}
set
{
ViewState["sortOrder"] = value;
}
}
protected void btnDownload_Click(object sender, EventArgs e)
{
ArrayList filelist = new ArrayList();
string fileLoc = "";
foreach (GridViewRow gr in gvAudioFiles.Rows)
{
CheckBox chkSelect = (CheckBox)gr.FindControl("chkSelect");
if (chkSelect.Checked)
{
HyperLink hlnkAudio = (HyperLink)gr.FindControl("hlnkAudio");
fileLoc = fileLoc + hlnkAudio.NavigateUrl + ",";
filelist.Add(hlnkAudio.NavigateUrl);
//SqlParameter S1=new SqlParameter(
}
}
if (filelist.Count != 0)
{
fileLoc = fileLoc.TrimEnd(',');
string zipFullPath = Server.MapPath("~\\") + "DocumentFolder.zip";
ZipOutputStream zipOut = new ZipOutputStream(File.Create(zipFullPath));
//string[] f = { "D:\\Projects\\STREAMLINEMT\\StreamMT\\AudioFiles\\linder\\Friday, November 27, 2009\\162-13742409.VW", "D:\\Projects\\STREAMLINEMT\\StreamMT\\AudioFiles\\linder\\Friday, November 27, 2009\\13742409.Wav.wav" };
for (int i = 0; i < fi =" new" entry =" new" sreader =" File.OpenRead(Server.MapPath(filelist[i].ToString()));" buff =" new" datetime =" fi.LastWriteTime;" size =" sReader.Length;" postbackurl = "AssignAudioFilesToQA.aspx?stat=audi" stat="audi" targetf =" new" contenttype = "application/zip" filename=" + targetf.Name); FileStream sourceFile = new FileStream(@fapth, FileMode.Open); long FileSize; FileSize = sourceFile.Length; byte[] getContent = new byte[(int)FileSize]; sourceFile.Read(getContent, 0, (int)sourceFile.Length); sourceFile.Close(); Response.BinaryWrite(getContent); } private void exporttoGrid() { Response.Clear(); Response.AddHeader(" filename="FileName.xls" charset = "" contenttype = "application/vnd.xls" stringwrite =" new" htmlwrite =" new" ld =" GetDSSDuration(" popupscript = "