Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# ASP.NET
I am new to unit testing; it would be helpful if anybody can guide to conduct unit testing for my database driven website.

This website’s job is to upload/transfer data from excel to database and display records in the Grid View. If user wants to make some changes e.g. Edit/Delete to the data, he/she will be able to do it through GridView.

I want to use NUnit, for conducting unit test for my web page.

I am also attaching the code for reference. If anybody can help me in this issue it would be really helpful.

Thanks in advance.

Dipak
 
My Code
-------------
 
public partial class _Default : System.Web.UI.Page 
{ 
    string connectionString; 
    //connectionString = "Integrated Security=SSPI;Persist Security Info=False;User ID=sa;Initial Catalog=TEST_DB;Data Source=DELL-PC"; 
 
 
    protected void Page_Load(object sender, EventArgs e) 
    { 
        if (!IsPostBack) 
        { 
            this.BindData(); 
 
        } 
        else { GetData(); } 
 
 
    } 
    protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e) 
    { 
        if (e.Row.RowType == DataControlRowType.DataRow) 
        { 
            /* Row Color + Hover */ 
            e.Row.Attributes["onmouseover"] = "javascript:setMouseOverColor(this);"; 
            e.Row.Attributes["onmouseout"] = "javascript:setMouseOutColor(this);"; 
 
 
        } 
    } 
    protected void btnUpload_Click(object sender, EventArgs e) 
    { 
        //string connectionString = ""; 
        if (FileUpload1.HasFile) 
        { 
            //string fileName = Path.GetFileName(FileUpload1.PostedFile.FileName); 
 
            //string fileExtension = Path.GetExtension(FileUpload1.PostedFile.FileName); 
            //string fileLocation = Server.MapPath("~/App_Data/" + fileName); 
            //string fileLocation = Server.MapPath("~/App_Data/Excel_File/" + fileName); 
 
 
            //FileUpload1.SaveAs(fileLocation); 
 
            string fileName = Filechecker(); 
            string fileLocation = Server.MapPath("~/App_Data/Excel_File/" + fileName); 
 
            DataSet objds = new DataSet(); 
            objds = ImportFromExcel(fileName, fileLocation); 
            foreach (System.Data.DataTable dt in objds.Tables) 
            { 
 
 
 
                //string connectionString; 
                connectionString = ""; 
                connectionString = "Integrated Security=SSPI;Persist Security Info=False;User ID=sa;Initial Catalog=TEST_DB;Data Source=DELL-PC"; 
 
                //Open a connection with destination database; 
                using (SqlConnection connection = 
                               new SqlConnection(connectionString)) 
                { 
                    connection.Open(); 
 
 
 
 
                    SqlBulkCopyColumnMapping mapping1 = new SqlBulkCopyColumnMapping(0, "cust_name"); 
                    SqlBulkCopyColumnMapping mapping2 = new SqlBulkCopyColumnMapping(1, "cust_address"); 
                    SqlBulkCopyColumnMapping mapping3 = new SqlBulkCopyColumnMapping(2, "cust_state"); 
 
 
                    //Open bulkcopy connection. 
                    using (SqlBulkCopy bulkcopy = new SqlBulkCopy(connection)) 
                    { 
 
 
 
 
                        bulkcopy.ColumnMappings.Add(mapping1); 
                        bulkcopy.ColumnMappings.Add(mapping2); 
                        bulkcopy.ColumnMappings.Add(mapping3); 
 
                        //Set destination table name 
                        //to table previously created. 
                        bulkcopy.DestinationTableName = "dbo.customer"; 
                        //Define column mappings 
 
 
                        try 
                        { 
                            bulkcopy.WriteToServer(dt); 
                            this.BindData(); 
                        } 
                        catch (Exception ex) 
                        { 
                            Page.Response.Write(ex.Message); 
                        } 
 
                        connection.Close(); 
 
                    } 
                } 
            } 
 
        } 
    } 
    public DataSet ImportFromExcel(string file, string filepath) 
    { 
        // Create new dataset 
        DataSet ds = new DataSet(); 
 
        // -- Start of Constructing OLEDB connection string to Excel file 
        Dictionary<string,> props = new Dictionary<string,>(); 
 
        // For Excel 2007/2010 
        if (file.EndsWith(".xlsx")) 
        { 
            props["Provider"] = "Microsoft.ACE.OLEDB.12.0;"; 
            props["Extended Properties"] = "Excel 12.0 XML"; 
        } 
        // For Excel 2003 and older 
        else if (file.EndsWith(".xls")) 
        { 
            props["Provider"] = "Microsoft.Jet.OLEDB.4.0"; 
            props["Extended Properties"] = "Excel 8.0"; 
        } 
        else 
            return null; 
 
        props["Data Source"] = filepath; 
 
        StringBuilder sb = new StringBuilder(); 
 
        foreach (KeyValuePair<string,> prop in props) 
        { 
            sb.Append(prop.Key); 
            sb.Append('='); 
            sb.Append(prop.Value); 
            sb.Append(';'); 
        } 
 
        string connectionString = sb.ToString(); 
        // -- End of Constructing OLEDB connection string to Excel file 
 
        // Connecting to Excel File 
        using (OleDbConnection conn = new OleDbConnection(connectionString)) 
        { 
            conn.Open(); 
            OleDbCommand cmd = new OleDbCommand(); 
            cmd.Connection = conn; 
 
            // Get all Sheets in Excel File 
            DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); 
 
            // Loop through all Sheets to get data 
            foreach (DataRow dr in dtSheet.Rows) 
            { 
                string sheetName = dr["TABLE_NAME"].ToString(); 
 
                // Get all rows from the Sheet 
                cmd.CommandText = "SELECT * FROM [" + sheetName + "]"; 
 
                DataTable dt = new DataTable(); 
                dt.TableName = sheetName.Replace("$", string.Empty); 
 
                OleDbDataAdapter da = new OleDbDataAdapter(cmd); 
                da.Fill(dt); 
 
                // Add table into DataSet 
                ds.Tables.Add(dt); 
            } 
 
            cmd = null; 
            conn.Close(); 
        } 
 
        return ds; 
    } 
    /*------------------------------------------------------------------------------------------------------------------------*/ 
    private void BindData() 
    { 
        string strQuery = "select customer.cust_id,customer.cust_name," + 
                           "customer.cust_address,customer.date_of_sale," + 
                           "tbl_state.state_name" + 
                           " from customer, tbl_state where" + 
                           " customer.cust_state=tbl_state.state_id"; 
 
        SqlCommand cmd = new SqlCommand(strQuery); 
        //Page.Response.Write(strQuery); 
        GridView1.DataSource = GetData(cmd); 
        GridView1.DataBind(); 
    } 
 
    private DataTable GetData(SqlCommand cmd) 
    { 
 
        //string connectionString; 
        connectionString = ""; 
        connectionString = "Integrated Security=SSPI;Persist Security Info=False;User ID=sa;Initial Catalog=TEST_DB;Data Source=DELL-PC"; 
 
 
        DataTable dt = new DataTable(); 
        using (SqlConnection con = new SqlConnection(connectionString)) 
        { 
            using (SqlDataAdapter sda = new SqlDataAdapter()) 
            { 
                cmd.Connection = con; 
                con.Open(); 
                sda.SelectCommand = cmd; 
                sda.Fill(dt); 
                return dt; 
            } 
        } 
    } 
 
    protected void OnPaging(object sender, GridViewPageEventArgs e) 
    { 
        this.BindData(); 
        GridView1.PageIndex = e.NewPageIndex; 
        GridView1.DataBind(); 
        SetData(); 
    } 
    protected void Delete(object sender, EventArgs e) 
    { 
 
    } 
    protected void Edit(object sender, EventArgs e) 
    { 
        using (GridViewRow row = (GridViewRow)((LinkButton)sender).Parent.Parent) 
        { 
            this.txtID.ReadOnly = true; 
            this.txtID.Text = row.Cells[1].Text; 
            this.txtName.Text = row.Cells[2].Text; 
            this.txtAddress.Text = row.Cells[3].Text; 
            this.txtdate.Text = row.Cells[4].Text; 
            connectionString = ""; 
            connectionString = "Integrated Security=SSPI;Persist Security Info=False;User ID=sa;Initial Catalog=TEST_DB;Data Source=DELL-PC"; 
            DataSet ds = new DataSet(); 
            SqlConnection con = new SqlConnection(connectionString); 
            con.Open(); 
            SqlCommand sqlcmd = new SqlCommand("select state_id,state_name from tbl_state", con); 
            SqlDataAdapter da = new SqlDataAdapter(sqlcmd); 
            da.Fill(ds); 
            this.ddState.DataSource = ds; 
            this.ddState.DataTextField = "state_name"; 
            this.ddState.DataValueField = "state_id"; 
            this.ddState.DataBind(); 
            this.ddState.Items.Insert(0, new ListItem("--Select--", "--Select--")); 
            this.ddState.Items.FindByText(row.Cells[5].Text).Selected = true; 
            //this.ddState.Text = row.Cells[4].Text; 
            popup.Show(); 
            con.Close(); 
        } 
    } 
 
    protected void Add(object sender, EventArgs e) 
    { 
        this.txtID.ReadOnly = false; 
        this.txtName.Text = string.Empty; 
        this.txtAddress.Text = string.Empty; 
        this.txtdate.Text = string.Empty; 
 
        // 
        //string connectionString; 
        connectionString = ""; 
        connectionString = "Integrated Security=SSPI;Persist Security Info=False;User ID=sa;Initial Catalog=TEST_DB;Data Source=DELL-PC"; 
        DataSet ds = new DataSet(); 
        SqlConnection con = new SqlConnection(connectionString); 
        con.Open(); 
        SqlCommand sqlcmd = new SqlCommand("select state_id,state_name from tbl_state", con); 
        SqlDataAdapter da = new SqlDataAdapter(sqlcmd); 
        da.Fill(ds); 
        this.ddState.DataSource = ds; 
        this.ddState.DataTextField = "state_name"; 
        this.ddState.DataValueField = "state_id"; 
        this.ddState.DataBind(); 
        this.ddState.Items.Insert(0, new ListItem("--Select--", "--Select--")); 
        popup.Show(); 
        con.Close(); 
    } 
 
    protected void Save(object sender, EventArgs e) 
    { 
 
        Page.Validate(valSummary.ValidationGroup); 
        if (!Page.IsValid) 
        { 
            update.Update(); 
            modalPopupEx.Show(); 
            popup.Show(); 
 
        } 
        else 
        { 
 
            using (SqlCommand cmd = new SqlCommand()) 
            { 
                cmd.CommandType = CommandType.StoredProcedure; 
                cmd.CommandText = "AddUpdateCust"; 
                cmd.Parameters.AddWithValue("@ID", this.txtID.Text); 
                cmd.Parameters.AddWithValue("@Name", this.txtName.Text); 
                cmd.Parameters.AddWithValue("@Address", this.txtAddress.Text); 
                cmd.Parameters.AddWithValue("@State", this.ddState.SelectedValue.ToString()); 
                GridView1.DataSource = this.GetData(cmd); 
                GridView1.DataBind(); 
            } 
        } 
    } 
    /*------------------------------------------------------------------------------------------------------------------------*/ 
 
    protected void btnDelete_Click(object sender, EventArgs e) 
    { 
        int count = 0; 
        SetData(); 
        GridView1.AllowPaging = false; 
        //GridView1.DataBind(); 
        ArrayList arr = (ArrayList)ViewState["SelectedRecords"]; 
        count = arr.Count; 
        for (int i = 0; i < this.GridView1.Rows.Count; i++) 
        { 
            if (arr.Contains(GridView1.DataKeys[i].Value)) 
            { 
                DeleteRecord(GridView1.DataKeys[i].Value.ToString()); 
                arr.Remove(GridView1.DataKeys[i].Value); 
            } 
        } 
        ViewState["SelectedRecords"] = arr; 
        hfCount.Value = "0"; 
        GridView1.AllowPaging = true; 
        BindData(); 
        ShowMessage(count); 
    } 
    private void GetData() 
    { 
 
        ArrayList arr; 
 
        if (ViewState["SelectedRecords"] != null) 
 
            arr = (ArrayList)ViewState["SelectedRecords"]; 
 
        else 
 
            arr = new ArrayList(); 
 
        CheckBox chkAll = (CheckBox)GridView1.HeaderRow 
 
                            .Cells[0].FindControl("chkAll"); 
 
        for (int i = 0; i < GridView1.Rows.Count; i++) 
        { 
 
            if (chkAll.Checked) 
            { 
 
                if (!arr.Contains(GridView1.DataKeys[i].Value)) 
                { 
 
                    arr.Add(GridView1.DataKeys[i].Value); 
 
                } 
 
            } 
 
            else 
            { 
 
                CheckBox chk = (CheckBox)GridView1.Rows[i] 
 
                                   .Cells[0].FindControl("chk"); 
 
                if (chk.Checked) 
                { 
 
                    if (!arr.Contains(GridView1.DataKeys[i].Value)) 
                    { 
 
                        arr.Add(GridView1.DataKeys[i].Value); 
 
                    } 
 
                } 
 
                else 
                { 
 
                    if (arr.Contains(GridView1.DataKeys[i].Value)) 
                    { 
 
                        arr.Remove(GridView1.DataKeys[i].Value); 
 
                    } 
 
                } 
 
            } 
 
        } 
 
        ViewState["SelectedRecords"] = arr; 
 
    } 
    private void SetData() 
    { 
 
        int currentCount = 0; 
 
        CheckBox chkAll = (CheckBox)GridView1.HeaderRow 
 
                                .Cells[0].FindControl("chkAll"); 
 
        chkAll.Checked = true; 
 
        ArrayList arr = (ArrayList)ViewState["SelectedRecords"]; 
 
        for (int i = 0; i < GridView1.Rows.Count; i++) 
        { 
 
            CheckBox chk = (CheckBox)GridView1.Rows[i] 
 
                            .Cells[0].FindControl("chk"); 
 
            if (chk != null) 
            { 
 
                chk.Checked = arr.Contains(GridView1.DataKeys[i].Value); 
 
                if (!chk.Checked) 
 
                    chkAll.Checked = false; 
 
                else 
 
                    currentCount++; 
 
            } 
 
        } 
 
        hfCount.Value = (arr.Count - currentCount).ToString(); 
 
    } 
 
    private void ShowMessage(int count) 
    { 
        StringBuilder sb = new StringBuilder(); 
        sb.Append("<script type = 'text/javascript'>"); 
        sb.Append("alert('"); 
        sb.Append(count.ToString()); 
        sb.Append(" records deleted.');"); 
        sb.Append("</script>"); 
        ClientScript.RegisterStartupScript(this.GetType(), 
                        "script", sb.ToString()); 
    } 
 
 
    private void DeleteRecord(string CustomerID) 
    { 
        //string connectionString; 
        connectionString = ""; 
        connectionString = "Integrated Security=SSPI;Persist Security Info=False;User ID=sa;Initial Catalog=TEST_DB;Data Source=DELL-PC"; 
        string query = "delete from customer " + 
                        "where cust_id=@CustomerID"; 
        SqlConnection con = new SqlConnection(connectionString); 
        SqlCommand cmd = new SqlCommand(query, con); 
        cmd.Parameters.AddWithValue("@CustomerID", CustomerID); 
        con.Open(); 
        cmd.ExecuteNonQuery(); 
        con.Close(); 
    } 
    /*--------------------------------------------------------------------------------------------------------*/ 
    private string Filechecker() 
    { 
        try 
        { 
 
            string UpPath = Server.MapPath("~/App_Data/Excel_File/"); 
            //the directory you want the file uploaded to 
            //create directory if it doesn't already exist 
            if (!Directory.Exists(UpPath)) 
                Directory.CreateDirectory(UpPath.Replace("\\", "\\\\")); 
            string FileName = FileUpload1.PostedFile.FileName; 
            string FileType = FileUpload1.PostedFile.ContentType; 
            string FileSize = FileUpload1.PostedFile.ContentLength.ToString(); 
            string ClientFile = System.IO.Path.GetFileName(FileName); 
            FileInfo ServerFile = new FileInfo(UpPath.Replace("\\", "\\\\") + "\\" + ClientFile); 
 
            int idx = 1; 
            //increment a number on the end of the filename until it is unique to stop files being overwritten 
            //will cope with filenames with multiple dots (eg. sunset.photo.12.jpg) 
            while (ServerFile.Exists) 
            { 
                string[] filename = ClientFile.Split('.'); 
                ClientFile = ""; 
                int prevIdx = idx - 1; 
                if (idx > 1) 
                { 
                    for (int i = 0; i < filename.GetUpperBound(0); i++) 
                    { 
                        ClientFile += filename[i]; 
                        if (i < filename.GetUpperBound(0) - 1) 
                            ClientFile += "."; 
                    } 
                    ClientFile = ClientFile.Substring(0, ClientFile.Length - prevIdx.ToString().Length) + idx.ToString() + "." + filename[filename.GetUpperBound(0)]; 
                } 
                else 
                { 
                    for (int i = 0; i < filename.GetUpperBound(0); i++) 
                    { 
                        ClientFile += filename[i]; 
                        if (i < filename.GetUpperBound(0) - 1) 
                            ClientFile += "."; 
                    } ClientFile += idx.ToString() + "." + filename[filename.GetUpperBound(0)]; 
                } 
                ServerFile = new FileInfo(UpPath.Replace("\\", "\\\\") + "\\" + ClientFile); 
                idx++; 
            } 
            //save file to server 
            FileUpload1.PostedFile.SaveAs(UpPath.Replace("\\", "\\\\") + "\\" + ClientFile); return ClientFile; 
        } 
        catch (Exception ex) { throw ex; } 
    } 
 
 
 
    protected void btnExport_Click(object sender, EventArgs e) 
    { 
         
        Response.Clear(); 
        Response.AddHeader("content-disposition","attachment;filename=FileName.xls"); 
        Response.Charset = ""; 
 
        // If you want the option to open the Excel file without saving than 
        // comment out the line below 
        // Response.Cache.SetCacheability(HttpCacheability.NoCache); 
 
        Response.ContentType = "application/vnd.xls"; 
        System.IO.StringWriter stringWrite = new System.IO.StringWriter(); 
 
        System.Web.UI.HtmlTextWriter htmlWrite = 
        new HtmlTextWriter(stringWrite); 
 
        // turn off paging 
        GridView1.AllowPaging = false; 
        //BindData(); 
 
        if (this.txtSearch.Text == "") 
        { BindData(); } 
        else 
        { SearchText(); } 
             
         
        GridView1.RenderControl(htmlWrite); 
        Response.Write(stringWrite.ToString()); 
        Response.End(); 
 
        // turn the paging on again 
        GridView1.AllowPaging = true; 
        //BindData(); 
        if (this.txtSearch.Text == "") 
        { BindData(); } 
        else 
        { SearchText(); } 
 
 
    } 
    public override void VerifyRenderingInServerForm(Control control) 
    { 
        //this.VerifyRenderingInServerForm(this.GridView1); 
    } 
 
    protected void txtSearch_TextChanged(object sender, EventArgs e) 
    { 
        SearchText(); 
    } 
    private void SearchText() 
    { 
        string strQuery = "select customer.cust_id,customer.cust_name," + 
                           "customer.cust_address,customer.date_of_sale," + 
                           "tbl_state.state_name" + 
                           " from customer, tbl_state where" + 
                           " customer.cust_state=tbl_state.state_id And tbl_state.state_name='" + this.txtSearch.Text + "'"; 
 
        SqlCommand cmd = new SqlCommand(strQuery); 
        //Page.Response.Write(strQuery); 
          GridView1.DataSource = GetData(cmd); 
          GridView1.DataBind(); 
    } 
}
Posted 13-Jan-13 13:56pm
Edited 13-Jan-13 14:03pm
v2

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

  Permalink  
v2
Comments
Member 8352335 at 14-Jan-13 7:38am
   
Thanx for ur reply, but it would be very helpfull if u can provide me some other link or code were Nunit has been implemented eg how to test fetching data from database.
 
Thanx,
Dipak
Abhishek Pant at 14-Jan-13 13:54pm
   
hi Member 8352335, check my updated answer.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 7,130
1 DamithSL 5,089
2 Maciej Los 4,866
3 Sergey Alexandrovich Kryukov 4,647
4 Kornfeld Eliyahu Peter 4,409


Advertise | Privacy | Mobile
Web02 | 2.8.141223.1 | Last Updated 14 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100