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>

 

    <% } %>