Export data SQL to Excel

আমরা যদি Dadabase(SQL) থেকে Excel Data export করতে চাই তবে নিচের Method Button Click  এ লিখলে .csv file export হবে।

এখানে নির্দিষ্ট range এর ভিতর data export দেখান হল।

protected void ButtonExport_Click(object sender, EventArgs e)

{

 int startId = 0;

int.TryParse(StartId.Text, out startId);/*Text Box Text Start*/

int endId = 0;

int.TryParse(EndId.Text, out endId);/*Text Box Text End*/

List<string> listString = new List<string>();

MemberCollection memberColl = new MemberController().FetchAll();/*Table এর সব data নিয়ে আসবে*/

listString.Add("Id," + "Email," + "FirstName," + "LastName," + "Country,");

foreach (Member member in memberColl)

 {

int memberId = member.Id;

if (memberId >= startId && memberId <= endId)

{

listString.Add(member.Id.ToString() + "," + member.Email + ","

+ member.FirstName + "," + member.LastName + "," + member.Country);

}

}

ExportTestResult(listString, "report");

}

private void ExportTestExcel(List<string> report, string downloadFileName)

{

downloadFileName = downloadFileName.Replace(" ", "-");

string fileName = Server.MapPath("~/") + Guid.NewGuid().ToString() + ".csv";

using (StreamWriter sw = new StreamWriter(fileName))

 {

foreach (string s in report)

{

sw.WriteLine(s);

}

sw.Flush();

                }

                using (StreamReader sr = new StreamReader(fileName))

                {

Response.Clear();

Response.AddHeader("Content-Disposition", "attachment; filename=" + downloadFileName + ".csv");

Response.Write(sr.ReadToEnd());

Response.ContentType = "text/csv";

Response.Flush();

Response.End();

}

 File.Delete(fileName);

}

সব শেষে server এ তৈরি হওয়া file টি আমরা delete করে ফেলব।

Validation Check @ MVC 2

ASP.NET MVC2 Validation Check ২ ভাবে করা যায়

JavaScript এর মাধ্যমে Client side এ এবং Controller এর মাধ্যমে Server Side . তবে ২ ধরনের Validation রাখা ভালো. কারণ  Client side  এর Browser JavaScript Enable না থাকলে Client side validation show করবে না।

আমরা এখানে Server side Validation দেখাব।

Controller Page [StudentController.cs]
===============================================

StudentRepository studentRepositoryObj = new StudentRepository();

[LINQ to SQL Classes. আমরা এ class er Object এর মাধ্যমে Data Save  করব]

 

//[অনেক ভাবে post Data receive করা যায়. আমরা এখানে FormCollection formValus মাধ্যমে Data receive করব।]

  //

        // POST: /Student/Create

        [AcceptVerbs(HttpVerbs.Post)]

        public ActionResult Create(FormCollection formValus)

        {

            ViewData["groupName"] = LoadDropDownList();

            Student studentObj = new Student();

            studentObj.Status = true;

            studentObj.Name = Request.Form["Name"].Trim();

            studentObj.Age = Convert.ToInt16(Request.Form["Age"]);

            studentObj.Bio = Request.Form["Bio"];

            studentObj.Type = Request.Form["Type"];

            string status = Request.Form["Status"];

            string status3 = Request.Form.GetValues("Status")[1];

            bool state;

            if (bool.TryParse(status, out state))

            {

                studentObj.Status = state;

            }     

            string status2 = Request.Form["IsActive"];

            if (studentObj.Name.Length== 0)

            {

                ModelState.AddModelError("Name", "Name is required.");

            }

            if (studentObj.Age < 18 || studentObj.Age > 120)

            {

                ModelState.AddModelError("Age", "Age between 18 to 120.");

            }

            if (studentObj.Bio.Trim().Length == 0)

            {

                ModelState.AddModelError("Bio", "Please Post Bio.");

            }

            if (studentObj.Type.Length == 0)

            {

                ModelState.AddModelError("Type", "Group Not Selected.");

            }

 

            if (bool.TryParse(status2, out state))

            {

                studentObj.IsActive = state;

            }

            else

            {

                ModelState.AddModelError("IsActive", "Tick on Activity");

            }

            if (!ModelState.IsValid)

            {               

                return View("Create", studentObj);

            }

            studentRepositoryObj.Add(studentObj);

            studentRepositoryObj.Save();

            return RedirectToAction("Index");

        }

 

Validation Check এর জন্য আমরা প্রত্যকটি data receive করে Check করব যে ঠিক মত আছে কিনা। যদি না থাকে তবে ModelState.AddModelErrorADD  করে রাখব।

ModelState.AddModelError("Age", "Age between 18 to 120.");

 

AddModelError এর টি parameter আছে।

প্রথমটি Object property অথবা name.

২য়টি আমরা যে Message Show ক রতে চাই তা।

 

ModelState যদি Valid না হয় তবে আমরা আবার View Page Object নিয়ে যাবো এবং Error message Show করব Html.ValidationSummary() এবং Html.ValidationMessage("Name","*") এর মাধ্যমে।

আর যদি Valid হয় তবে studentRepositoryObj Object এর মাধ্যমে Save ক রব এবং index page Redirect ক রে চলে যাব।

 

View Page [Create.cs]
===============================================
<% using (Html.BeginForm()) {%>

 

        <%List<string> groups = ViewData["groupName"] as List<string>; %>

        <% var studentObj = ViewData.Model as StudentInfo; %>

        <%= Html.ValidationSummary() %> <%--Display Controller Message--%>

        <fieldset>

            <legend>New Student</legend>

<P>

                      <%= Html.Label("Name") %>:

<%= Html.TextBox("Name")%>

<%= Html.ValidationMessage("Name","*") %>

               </p>

              <p>

<%= Html.Label("Age") %>:

<%= Html.TextBox("Age") %>

<%= Html.ValidationMessage("Age", "*") %>

              </p>

              <p>

                        <%= Html.Label("Bio") %>:

<%= Html.TextArea("Bio","",5,23,null) %>

<%= Html.ValidationMessage("Bio", "*") %>

</p>

              <p>

                        <%= Html.Label("Type") %>:

<%= Html.DropDownList("Type", new SelectList(type), "Select")%>

<%=Html.ValidationMessage("Type", "Select Group")%>

</p>

              <p>

<%= Html.Label("Status") %>:

<%= Html.CheckBox("Status")%>

<%= Html.CheckBox("Status")%>

</p>

              <p>

Is Active:

<%= Html.RadioButton("IsActive","True")%>Yes

<%= Html.RadioButton("IsActive", "False")%>No

<%=Html.ValidationMessage("IsActive", "Activity not Selected") %>

</p>

<p>

                        <input type="submit" value="Create" />

</p>

        </fieldset>

 

    <% } %>



HTML Helper (ASP.NET MVC2)

<%= Html.Label("Name") %> 
<%= Html.TextBox("Name")%>                   
<%= Html.ValidationMessage("Name","*")%>                    
<%= Html.TextArea("Bio","Input Here",5,23,null) %>
<%= Html.DropDownList("Type", new SelectList(type), "Select")%>
<%= Html.CheckBox("Status")%>True
<%= Html.CheckBox("Status")%>False
<%= Html.CheckBox("Status2")%>No Comment
<%= Html.RadioButton("IsActive","True")%>Yes
<%= Html.RadioButton("IsActive", "False")%>No
<%= Html.ActionLink("Back to List", "Index") %>//Applied on Same Controller
<%=Html.ActionLink("Add New Project", "AddProject", "Project")%>//Display Text, Action Link, Controller Name

Validation Check (ASP.NET MVC 2)

Controller Page[StudentController.cs]
===============================================
StudentRepository studentRepositoryObj = new StudentRepository();    //LINQ to SQL Classes
[AcceptVerbs(HttpVerbs.Post)]
public ActionResult Create(FormCollection formValus)
{
    StudentInfo studentObj = new StudentInfo();

    studentObj.Name = Request.Form["Name"].Trim();
    if (studentObj.Name.Length == 0)
    {
        ModelState.AddModelError("Name", "Name is required.");    
    }

    studentObj.Age = Convert.ToInt16(Request.Form["Age"]);
    if (studentObj.Age < 69 || studentObj.Age > 420)
    {
        ModelState.AddModelError("Age", "Age between 69 to 420.");
    }

    studentObj.Type = Request.Form["Type"];
    if (studentObj.Type.Length == 0)
    {
        ModelState.AddModelError("Type", "Type Not Selected.");
    }

    string status = Request.Form["Status"];
    bool state;
    if (bool.TryParse(status, out state))
    {
        studentObj.Status = state;
    }

    string isActive = Request.Form["IsActive"];          
    if (bool.TryParse(isActive, out state))
    {
        studentObj.IsActive = state;
    }
    else
    {
        ModelState.AddModelError("IsActive", "Tick on Activity");
    }

    if (!ModelState.IsValid)
    {    
        return View("Create", studentObj);
    }
    studentRepositoryObj.Add(studentObj);
    studentRepositoryObj.Save();
    return RedirectToAction("Index");
}

View Page [Create.cs]
===============================================
<% using (Html.BeginForm()) {%>
    <%= Html.ValidationSummary("An Error Occured") %><%--Display All Controller Message if Field is Balnk--%>
        
        <p>
            <%= Html.Label("Name") %>:
            <%= Html.TextBox("Name")%>
            <%= Html.ValidationMessage("Name","*") %>
        </p>
        <p>
            <%= Html.Label("Age") %>:              
            <%= Html.TextBox("Age") %>
            <%= Html.ValidationMessage("Age", "*") %>
        </p>    
            <%= Html.Label("Type") %>:
            <%= Html.DropDownList("Type", new SelectList(type), "Select")%>
            <%=Html.ValidationMessage("Type", "Select Type") %>
        <p>
        </p>    
            <%= Html.Label("Status") %>:
            <%= Html.CheckBox("Status")%>
        </p>
        <p>
            Is Active:
            <%= Html.RadioButton("IsActive","True")%>Yes
            <%= Html.RadioButton("IsActive", "False")%>No
            <%=Html.ValidationMessage("IsActive", "Activity not Selected") %>
        </p>
        <p>
            <input type="submit" value="Create" />
        </p>
        
<% } %>

SubSonic App.Config File

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <configSections>
    <section name="SubSonicService" type="SubSonic.SubSonicSection, SubSonic" requirePermission="false"/>
  </configSections>
  <connectionStrings>
    <add name="DatabaseConnection" connectionString="Server=.\SQLEXPRESS;Database=WinForm;Trusted_Connection=yes"/>
  </connectionStrings>
  <SubSonicService defaultProvider="CustomerDatabase">
    <providers>
      <clear/>
      <add name="CustomerDatabase" type="SubSonic.SqlDataProvider, SubSonic" connectionStringName="DatabaseConnection" generatedNamespace="CustomerDatabase"/>
    </providers>
  </SubSonicService>
</configuration>

For More SubSonic Help

Connection String in ASP.NET

string connString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

Where ConnectionString=Name of commection string in web.config file.