Friday, November 14, 2014

3 Tier Architecture with File Upload Controll

=============================
3 Tier Architecture
=============================
Designe - design file .aspx
=============================
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="IUDFor.aspx.cs" Inherits="IUDFor" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <center>
   
    <div>
    <table>
    <tr>
        <td colspan="3" align="center"> EMPLOYEE FORM</td>
    </tr>
    <tr>
        <td>Name :</td>
        <td colspan="2"><asp:TextBox ID="txtName" runat="server"></asp:TextBox></td>
    </tr>
    <tr>
        <td>City :</td>
        <td colspan="2"><asp:TextBox ID="txtCity" runat="server"></asp:TextBox></td>
    </tr>
    <tr>
        <td>Salary :</td>
        <td colspan="2"><asp:TextBox ID="txtSal" runat="server"></asp:TextBox></td>
    </tr>
    <tr>
        <td>DOJ :</td>
        <td colspan="2"><asp:TextBox ID="txtDoj" runat="server"></asp:TextBox></td>
    </tr>

    <tr>
        <td>Photo Upload :</td>
        <td><asp:FileUpload ID="FilePhoto" runat="server" /></td>
        <td><asp:Image ID="Image1" runat="server" Height="30px" Width="50px" /></td>
    </tr>
    <tr>
        <td><asp:Button ID="btnSave" runat="server" Text="Save" onclick="btnSave_Click" />&nbsp;&nbsp;&nbsp;<asp:Button
                ID="btnUpdate" runat="server" Text="Update" onclick="btnUpdate_Click" /></td>
        <td><asp:Button ID="btnDelete" runat="server" Text="Delete"
                onclick="btnDelete_Click" />&nbsp;&nbsp;&nbsp;<asp:Button ID="btnCancel"
                runat="server" Text="Cancel" onclick="btnCancel_Click" /></td>
    </tr>
    </table>
   
    </div>
        <asp:GridView ID="GRVEMP1" runat="server" AutoGenerateColumns="False"
            BackColor="White" BorderColor="#999999" BorderStyle="Solid" BorderWidth="1px"
            CellPadding="3" EnableModelValidation="True" ForeColor="Black"
            GridLines="Vertical" DataKeyNames="Id" onrowdeleting="GRVEMP1_RowDeleting">
            <AlternatingRowStyle BackColor="#CCCCCC" />
            <FooterStyle BackColor="#CCCCCC" />
            <HeaderStyle BackColor="Black" Font-Bold="True" ForeColor="White" />
            <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
            <SelectedRowStyle BackColor="#000099" Font-Bold="True" ForeColor="White" />
           
            <Columns>
           
            <asp:TemplateField HeaderText="ID">
            <ItemTemplate>
                <asp:Label ID="lblId" runat="server" Text='<%# Eval("Id") %>'></asp:Label>
            </ItemTemplate>
            </asp:TemplateField>

            <asp:TemplateField HeaderText="Name">
            <ItemTemplate>
                <asp:Label ID="lblName" runat="server" Text='<%# Eval("Name") %>'></asp:Label>
            </ItemTemplate>
            </asp:TemplateField>

            <asp:TemplateField HeaderText="City">
            <ItemTemplate>
                <asp:Label ID="lblCity" runat="server" Text='<%# Eval("City") %>'></asp:Label>
            </ItemTemplate>
            </asp:TemplateField>
           
            <asp:TemplateField HeaderText="Salary">
            <ItemTemplate>
                <asp:Label ID="lblSalary" runat="server" Text='<%# Eval("Salary") %>'></asp:Label>
            </ItemTemplate>
            </asp:TemplateField>

            <asp:TemplateField HeaderText="DOJ" >
            <ItemTemplate >
                <asp:Label ID="lblDoj" runat="server" Text='<%# Eval("Doj","{0:d}") %>'   ></asp:Label>
            </ItemTemplate>
            </asp:TemplateField>

            <asp:TemplateField HeaderText="Photo">
            <ItemTemplate>
                <asp:Image ID="ImagePhoto" runat="server" ImageUrl='<%# Eval("Photo")  %>' Height="30px" Width="50px" />
            </ItemTemplate>
            </asp:TemplateField>

            <asp:TemplateField HeaderText="Command">
            <ItemTemplate>
                <asp:Button ID="btnSelect" runat="server" Text="Select" CommandName="Delete" />
            </ItemTemplate>

            </asp:TemplateField>

            </Columns>
        </asp:GridView>
    </center>

    </form>
</body>
</html>
=============================
 Code file .aspx.cs
=============================
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;

public partial class IUDFor : System.Web.UI.Page
{
    BLL b = new BLL();
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            Fetch();
        }
        this.btnUpdate.Visible = true;
    }
    protected void Fetch()
    {
        GRVEMP1.DataSource = b.Select1();
        GRVEMP1.DataBind();
    }
    protected void btnSave_Click(object sender, EventArgs e)
    {
        //this.btnUpdate.Visible = true;
        //b.Id1 = Convert.ToInt32(tx.Text);
        b.Name1 = txtName.Text;
        b.City1 = txtCity.Text;
        b.Salary1 = Convert.ToInt32(txtSal.Text);
        b.Doj1 = Convert.ToDateTime(txtDoj.Text);
        string Serverpath = "~\\Upload/";
        if (FilePhoto.HasFile)
        {
            string name = FilePhoto.FileName;
            Serverpath = Serverpath +  name;
            FilePhoto.SaveAs(Server.MapPath(Serverpath));
        }
        b.Photo1 = Serverpath;
        b.Insert1();
        Fetch();
    }
    protected void btnUpdate_Click(object sender, EventArgs e)
    {
        b.Id1 = Convert.ToInt32(Session["id"].ToString());
        b.Name1 = txtName.Text;
        b.City1 = txtCity.Text;
        b.Salary1 = Convert.ToInt32(txtSal.Text);
        b.Doj1 = Convert.ToDateTime(txtDoj.Text);
        string Serverpath = "~\\Upload/";
        if (FilePhoto.HasFile)
        {
            string name = FilePhoto.FileName;
            Serverpath = Serverpath + name;
            FilePhoto.SaveAs(Server.MapPath(Serverpath));
        }
        b.Photo1 = Serverpath;
        b.Update1();
        Fetch();
    }
    protected void btnDelete_Click(object sender, EventArgs e)
    {
        b.Id1 = Convert.ToInt32(Session["id"].ToString());
        b.Delete1();
        Fetch();
    }
    protected void btnCancel_Click(object sender, EventArgs e)
    {
        txtName.Text = "";
        txtCity.Text = "";
        txtSal.Text = "";
        txtDoj.Text = "";
        Image1.ImageUrl = "";
    }
    protected void GRVEMP1_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        int id;
        id = Convert.ToInt16(GRVEMP1.DataKeys[e.RowIndex].Value);
        Session["Id"]= Convert.ToInt32(id);
        b.Id1 = Convert.ToInt32(id);
        DataSet ds = new DataSet();
        ds = b.SelectFetch();
        if (ds != null)
        {
            txtName.Text = ds.Tables[0].Rows[0][1].ToString();
            txtCity.Text = ds.Tables[0].Rows[0][2].ToString();
            txtSal.Text = ds.Tables[0].Rows[0][3].ToString();
            string dt = ds.Tables[0].Rows[0][4].ToString();
            string dt1 = dt.Substring(0,10);
            txtDoj.Text = dt1.ToString();
            Image1.ImageUrl = ds.Tables[0].Rows[0][5].ToString();

        }
    }

}
=============================
 BLL file - BLL.cs
=============================
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
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;

/// <summary>
/// Summary description for BLL
/// </summary>
public class BLL
{
    DAL d = new DAL();
public BLL()
{
//
// TODO: Add constructor logic here
//
}
    private int Id;

    public int Id1
    {
        get { return Id; }
        set { Id = value; }
    }
    private string Name;

    public string Name1
    {
        get { return Name; }
        set { Name = value; }
    }
    private string City;

    public string City1
    {
        get { return City; }
        set { City = value; }
    }
    private int Salary;

    public int Salary1
    {
        get { return Salary; }
        set { Salary = value; }
    }
    private DateTime Doj;

    public DateTime Doj1
    {
        get { return Doj; }
        set { Doj = value; }
    }
    private string Photo;

    public string Photo1
    {
        get { return Photo; }
        set { Photo = value; }
    }

    public void Insert1()
    {
        d.Insert(Name, City, Salary, Doj, Photo);
    }

    public void Update1()
    {
        d.Update(Id, Name, City, Salary,Doj,Photo);
    }

    public void Delete1()
    {
        d.Delete(Id);
    }

    public DataSet Select1()
    {
        DataSet ds = new DataSet();
        ds = d.select();
        return ds;
    }

    public DataSet SelectFetch()
    {
        DataSet ds = new DataSet();
        ds = d.selectfetch1(Id);
        return ds;
    }
}
=============================
 DAL file - DAL.cs
=============================
using System;
using System.Data;
using System.Configuration;
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 System.Data.SqlClient;

/// <summary>
/// Summary description for DAL
/// </summary>
public class DAL
{
public DAL()
{
//
// TODO: Add constructor logic here
//
}
    SqlConnection con = new SqlConnection("Data Source=VISION-PC;Initial Catalog=test;User ID=sa;Password=vision");

    public void CommanData(string str)
    {
        con.Open();
        SqlCommand cmd = new SqlCommand(str,con);
        cmd.CommandType = CommandType.Text;
        cmd.ExecuteNonQuery();
        con.Close();
    }

    public void Insert(string Name,string City,int Salary,DateTime Doj,string Photo)
    {
        con.Open();
        SqlCommand cmd = new SqlCommand("InsertData", con);
        cmd.Parameters.AddWithValue("@Name", Name);
        cmd.Parameters.AddWithValue("@City", City);
        cmd.Parameters.AddWithValue("@Salary", Salary);
        cmd.Parameters.AddWithValue("@Doj", Doj);
        cmd.Parameters.AddWithValue("@Photo", Photo);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.ExecuteNonQuery();
        con.Close();
    }

    public void Update(int Id, string Name, string City, int Salary, DateTime Doj, string Photo)
    {
        con.Open();
        SqlCommand cmd = new SqlCommand("UpdateData", con);
        cmd.Parameters.AddWithValue("@Id", Id);
        cmd.Parameters.AddWithValue("@Name", Name);
        cmd.Parameters.AddWithValue("@City", City);
        cmd.Parameters.AddWithValue("@Salary", Salary);
        cmd.Parameters.AddWithValue("@Doj", Doj);
        cmd.Parameters.AddWithValue("@Photo", Photo);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.ExecuteNonQuery();
        con.Close();
    }

    public void Delete(int Id)
    {
        con.Open();
        SqlCommand cmd = new SqlCommand("DeleteData", con);
        cmd.Parameters.AddWithValue("@Id", Id);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.ExecuteNonQuery();
        con.Close();
    }

    public DataSet select()
    {
        con.Open();
        DataSet ds = new DataSet();
        SqlDataAdapter da = new SqlDataAdapter("Select * from Emp", con);
        da.Fill(ds);
        con.Close();
        return ds;
    }

    public DataSet selectfetch1(int Id)
    {
        con.Open();
        DataSet ds = new DataSet();
        SqlDataAdapter da = new SqlDataAdapter("Select * from Emp where Id = "+ Id +"", con);
        da.Fill(ds);
        con.Close();
        return ds;
    }
}
=============================
 Store Procedure
=============================
 Insert SP - InsertData
=============================
USE [test]
GO
/****** Object:  StoredProcedure [dbo].[InsertData]    Script Date: 07/03/2013 12:17:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[InsertData]
@Name Varchar(50),
@City Varchar(50),
@Salary int,
@Doj DateTime,
@Photo Varchar(Max)
AS
BEGIN
insert into  Emp values ( @Name, @City, @Salary ,Convert(Datetime,@Doj,105),@Photo)
END
=============================
 Update SP - UpdateData
=============================
USE [test]
GO
/****** Object:  StoredProcedure [dbo].[UpdateData]    Script Date: 07/03/2013 12:17:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[UpdateData]
@Id int,
@Name Varchar(50),
@City Varchar(50),
@Salary int,
@Doj DateTime,
@Photo Varchar(Max)
AS
BEGIN
Update Emp set Name=@Name, City=@City, Salary=@Salary,Doj=CONVERT(DateTime,@Doj,105),Photo=@Photo where Id=@Id
END
=============================
 Delete SP - DeeleteData
=============================
USE [test]
GO
/****** Object:  StoredProcedure [dbo].[DeleteData]    Script Date: 07/03/2013 12:17:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[DeleteData]
@Id int

AS
BEGIN
Delete from Emp where Id = @Id
END
=============================
The End
=============================
GUI Output
-------------








at No comments:

Dynamic Store Procedure

Integer Auto Increment

CREATE PROCEDURE dbo.SP_INTAUTODETAIL
AS
BEGIN
select MAX(NID)+1 from
(
select isnull(MAX(<filed>),0) NID from <tblname>
) N
END

String Auto Increment

 CREATE PROCEDURE dbo.SP_STRAUTODETAIL

AS
BEGIN
--@NewsID is Filed Name
declare @id int

select @id =CONVERT(varchar,count(*)+1) from  <tblName>

if (@id <=9)
begin
select @NewsID='News00'+CONVERT(varchar,@id)
end
else if (@id <=99)
begin
select @NewsID='News0'+CONVERT(varchar,@id)
end
else
begin
select @NewsID='News'+CONVERT(varchar,@id)
end



END

Insert SP

  ALTER PROCEDURE [dbo].[SP_INSERT]
 @TABLENAME VarChar(100)=Null,
 @FIELDS VarChar(8000)=Null,
 @VALUES VarChar(8000)=Null
 AS
 BEGIN
   DECLARE @QUERY VARCHAR(8000)
   Set @Query = 'INSERT INTO '+ @TABLENAME + ' '
   Set @Query = @Query + '( ' + @FIELDS + ')' + ' VALUES '
    Set @Query = @Query + '( ' + @VALUES + ')'
   EXEC(@Query)

 END


Update SP

ALTER PROCEDURE [dbo].[sp_Update] 
@TableName VarChar(100) = '',
@Fields VarChar(8000) = '*',
@Criteria VarChar(8000) = Null 
AS 
 Begin 
  Declare @Stmt Varchar(8000) 

  Set @Stmt = '
Update ' + @TableName + ' 
Set ' + @Fields + ' 
Where 1 = 1 ' + @Criteria 
  Exec(@Stmt)
 End

Delete SP

ALTER PROCEDURE [dbo].[SP_DELETE] 
 @TABLENAME VarChar(100)=Null,
 @CRITERIA VarChar(8000)=Null 
 AS 
 BEGIN 
   DECLARE @QUERY VARCHAR(8000) 
   Set @Query = ' Delete From ' + @TABLENAME 
   Set @Query = @Query + ' WHERE 1 = 1 ' 
   Set @Query = @Query + @CRITERIA 
   EXEC(@Query)
 END


at No comments:

Email-SMS Coding

Default.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <style type="text/css">
        .style1
        {
            width: 150px;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <table style="width: 100%;">
            <tr>
                <td class="style1">
                    &nbsp;
                </td>
                <td>
                    &nbsp;
                </td>
                <td>
                    &nbsp;
                </td>
            </tr>
            <tr>
                <td class="style1">
                    &nbsp; masg</td>
                <td>
                    &nbsp;
                    <asp:TextBox ID="txtemail" runat="server"></asp:TextBox>
                </td>
                <td>
                    &nbsp;
                </td>
            </tr>
            <tr>
                <td class="style1">
                    &nbsp;
                    Mob. No.</td>
                <td>
                    &nbsp;
                    <asp:TextBox ID="txtMono" runat="server"></asp:TextBox>
                </td>
                <td>
                    &nbsp;
                </td>
            </tr>
            <tr>
                <td class="style1">
                    &nbsp;</td>
                <td>
                    <asp:Button ID="btnemail" runat="server" onclick="btnemail_Click"
                        Text="Send Email" />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                    <asp:Button ID="sms" runat="server" onclick="sms_Click" Text="Send SMS" />
                </td>
                <td>
                    &nbsp;</td>
            </tr>
        </table>
    </div>
    </form>
</body>

</html>


Default.asp.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Net;
using System.Net.Mail;
using System.IO;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void btnemail_Click(object sender, EventArgs e)
    {
            var smtp = new System.Net.Mail.SmtpClient();
            {
                smtp.Host = "smtp.gmail.com";
                smtp.Port = 587;
                smtp.EnableSsl = true;
                smtp.DeliveryMethod = System.Net.Mail.SmtpDeliveryMethod.Network;
                smtp.Credentials = new NetworkCredential("<yourEmailID>@gmail.com", "<your Password>");
                smtp.Timeout = 20000;
            }
            smtp.Send("abcd@gmail.com", txtemail.Text, "Friend REQUEST.", "<br> your mobile num. is : " + txtMono.Text);
            ScriptManager.RegisterStartupScript(this, this.GetType(), "alertmessage", "javascript:alert('* Successfuliy Send Mobile Num. in your EmailID...')", true);

    }
    protected void sms_Click(object sender, EventArgs e)
    {

        ////======================================//
        //// SMS Booking Confirm.
        ////======================================//
        ////string sms = " Mr./Mrs." + ds.Tables[0].Rows[0][5].ToString() + " Your Booking Request is Confirm. Your Requested type Number is " + ds.Tables[0].Rows[0][4].ToString() + ". ";
        ////string strUrl = "http://api.mVaayoo.com/mvaayooapi/MessageCompose?user=hit_patel_76@yahoo.com:hitpatel&senderID=TEST SMS&receipientno=" + ds.Tables[0].Rows[0][8].ToString() + "&dcs=0&msgtxt=" + sms + " &state=4";
        //string msg = "hi... Good morning..";
        //string strUrl = "http://api.mVaayoo.com/mvaayooapi/MessageCompose?user=<youracc>@yahoo.com:100420116024&senderID=TEST SMS&receipientno="+ txtMono.Text +"&msgtxt="+ msg +"&state=4";
        //WebRequest request = HttpWebRequest.Create(strUrl);
        //HttpWebResponse response = (HttpWebResponse)request.GetResponse();
        //Stream s = (Stream)response.GetResponseStream();
        //StreamReader readStream = new StreamReader(s);
        //string dataString = readStream.ReadToEnd();
        //response.Close();
        //s.Close();
        //readStream.Close();

        ////======================================//
        try
        {

            send("7894561230", "34560", txtemail.Text, txtMono.Text);
            Response.Write("message send successfully......");
        }
        catch
        {
            Response.Write("Error Occured!!!");
        }
    }
    public void send(string uid, string password, string message, string no)
    {
        HttpWebRequest myReq = (HttpWebRequest)WebRequest.Create("http://ubaid.tk/sms/sms.aspx?uid=" + uid + "&pwd=" + password + "&msg=" + message + "&phone=" + no + "&provider=fullonsms");
        HttpWebResponse myResp = (HttpWebResponse)myReq.GetResponse();
        System.IO.StreamReader respStreamReader = new System.IO.StreamReader(myResp.GetResponseStream());
        string responseString = respStreamReader.ReadToEnd();
        respStreamReader.Close();
        myResp.Close();
    }

}
at No comments:

Simple 3-tier Coding Pattern

Default.asp 


<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
   
        <table style="width:100%;">
            <tr>
                <td>
                    &nbsp;ID :-&nbsp;</td>
                <td>
                    <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
                </td>
                <td>
                    &nbsp;</td>
            </tr>
            <tr>
                <td>
                    Name :-</td>
                <td>
                    <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
                </td>
                <td>
                    &nbsp;</td>
            </tr>
            <tr>
                <td>
                    City :-</td>
                <td>
                    <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>
                </td>
                <td>
                    &nbsp;</td>
            </tr>
            <tr>
                <td>
                    Salary :-</td>
                <td>
                    <asp:TextBox ID="TextBox4" runat="server"></asp:TextBox>
                </td>
                <td>
                    &nbsp;</td>
            </tr>
            <tr>
                <td>
                    &nbsp;</td>
                <td>
                    <asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="Add" />
                </td>
                <td>
                    &nbsp;</td>
            </tr>
        </table>
   
    </div>
    <asp:GridView ID="GridView1" runat="server">
    </asp:GridView>
    </form>
</body>

</html>




Default.aspx.cs   


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
public partial class _Default : System.Web.UI.Page
{
    BAL b = new BAL();
    DataSet ds = new DataSet();
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            bind();
        }
    }
    public void bind()
    {
        ds.Tables.Add(b.GetData("EmpMst"));
        GridView1.DataSource = ds.Tables["EmpMst"];
        GridView1.DataBind();
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        b.Id = Convert.ToInt16(TextBox1.Text);
        b.Name = TextBox2.Text;
        b.City = TextBox3.Text;
        b.Salary = Convert.ToInt16(TextBox4.Text);
        b.SetData();
        bind();
    }
}



BAL.CS


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;


/// <summary>
/// Summary description for BAL
/// </summary>
public class BAL
{
    DAL d = new DAL();
public BAL()
{
//
// TODO: Add constructor logic here
//
}
    private int id;

    public int Id
    {
        get { return id; }
        set { id = value; }
    }
    private string name;

    public string Name
    {
        get { return name; }
        set { name = value; }
    }

    private string city;

    public string City
    {
        get { return city; }
        set { city = value; }
    }
    private int salary;

    public int Salary
    {
        get { return salary; }
        set { salary = value; }
    }
    public void SetData()
    {
        d.SetEmpData(id, name, city, salary);
    }
    public DataTable GetData(string strtable)
    {
        return d.GetEmpData(strtable);
    }
}

DAL.cs 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

/// <summary>
/// Summary description for DAL
/// </summary>
public class DAL
{
    SqlConnection cnn = new SqlConnection(ConfigurationManager.AppSettings["strcnn"].ToString());
    public DAL()
    {
        //
        // TODO: Add constructor logic here
        //
    }
    public void SetEmpData(int id, string name, string city, int salary)
    {
        cnn.Open();
        SqlCommand cmd = new SqlCommand("dbo.insUp", cnn);
        cmd.Parameters.AddWithValue("@id", id);
        cmd.Parameters.AddWithValue("@name", name);
        cmd.Parameters.AddWithValue("@city", city);
        cmd.Parameters.AddWithValue("@salary", salary);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.ExecuteNonQuery();
        cnn.Close();

    }
    public DataTable  GetEmpData(string strtable)
    {
        DataTable dt = new DataTable(strtable);
        cnn.Open();
        SqlCommand cmd = new SqlCommand("dbo.SelectData", cnn);
        cmd.CommandType = CommandType.StoredProcedure;
        SqlDataAdapter adp = new SqlDataAdapter(cmd);
        adp.Fill(dt);
        cnn.Close();
        return dt;
    }
}


at No comments:

Thursday, November 13, 2014

Insert Data using Uploading Excel file and upload image and set water mark

Demo.aspx



<body>
    <form id="form1" runat="server">
    <div>
    <table>
    <tr>
    <td><asp:FileUpload ID ="fu" runat ="server" /></td>
    <td><asp:Button ID ="btnupload" runat ="server" Text="Upload File" 
            onclick="btnupload_Click" /></td>
    </tr>
    </table>
    </div>
    </form>
</body>


Demo.aspx.cs


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Drawing.Drawing2D;
using System.Text;
using System.Drawing.Imaging;


public partial class excellupload : System.Web.UI.Page
{
    DataTable dt = new DataTable();
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void btnupload_Click(object sender, EventArgs e)
    {
        if (fu.HasFile)
        {
            if (fu.PostedFile.ContentType == "application/vnd.ms-excel" || fu.PostedFile.ContentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
            {
                string fileName = Path.Combine(Server.MapPath("~/localization"), Guid.NewGuid().ToString() + Path.GetExtension(fu.PostedFile.FileName));
                fu.PostedFile.SaveAs(fileName);

                string conString = "";
                string ext = Path.GetExtension(fu.PostedFile.FileName);
                if (ext.ToLower() == ".xls")
                {
                    conString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
                }
                else if (ext.ToLower() == ".xlsx")
                {
                    conString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
                }
                string query = "Select * from [Sheet1$]";
                OleDbConnection con = new OleDbConnection(conString);
                OleDbDataAdapter data = new OleDbDataAdapter(query, con);
                data.Fill(dt);
                int i = 0;
                File_Upload file = new File_Upload();
                for (i = 0; i < dt.Rows.Count; i++)
                {
                    string name = dt.Rows[i]["name"].ToString();
                    string email = dt.Rows[i]["email"].ToString();
                    string pass = dt.Rows[i]["Password"].ToString();
                    string mobile = dt.Rows[i]["mobile"].ToString();
                    string state = dt.Rows[i]["state"].ToString();
                    string city = dt.Rows[i]["city"].ToString();
                    string photo = dt.Rows[i]["Photo"].ToString();

                    string[] pathArr = photo.Split('\\');
                    string[] fileArr = pathArr.Last().Split('.');
                    string fName = fileArr[0].ToString();
                    Random rnd = new Random();
                    string fn = fName + "_" + rnd.Next(111, 999) + "_" + rnd.Next(111, 999) + ".jpg";
                    string path = "~/photo/" + fn;
                    string pat = Server.MapPath(path);
                    Wattermark w = new Wattermark();
                    Dal odal = new Dal();
                    System.Drawing.Image image = System.Drawing.Image.FromFile(photo);
                    Graphics graphics = Graphics.FromImage(image);
                    Font font = new Font("Times New Roman", 42.0f);
                    PointF point = new PointF(10, 10);
                    graphics.DrawString("Tusharsangani", font, Brushes.Red, point);
                    image.Save(pat);
                    odal.fetch("insert into Registeruser (name,E_id,password,mobile,state,city,photo) values('" + name + "','" + email + "','" + pass + "','" + mobile + "','" + state + "','" + city + "','" + path + "')");
                }
            }
        }
        else
        {

        }
    }


}
at No comments:

Insert ,update,delete ,paging and sorting In to gridview

Gridsorting.aspx



     <asp:GridView ID="grd" runat="server" AllowPaging="true" AllowSorting="true"  PageSize="10"
            AutoGenerateColumns="false" ShowFooter ="true"
            DataKeyNames="id" OnSorting="grd_Sorting" OnRowEditing="grd_RowEditing"
            onrowupdating="grd_RowUpdating" onrowcancelingedit="grd_RowCancelingEdit"
            onpageindexchanging="grd_PageIndexChanging"
            onrowdeleting="grd_RowDeleting">
            <Columns>
                <asp:TemplateField HeaderText="Name" SortExpression="name" FooterStyle-Width="200px"
                    ItemStyle-Width="200px">
                    <ItemTemplate>
                        <asp:Label ID="lblname" Text='<%#Eval("name") %>' runat="server"></asp:Label>
                    </ItemTemplate>
                    <EditItemTemplate>
                        <asp:TextBox ID="txtname" Text='<%#Eval("name") %>' runat="server"></asp:TextBox>
                        <asp:RequiredFieldValidator ID="rfvEditCame" ValidationGroup="Edit" runat="server"
                            SetFocusOnError="true" Display="Dynamic" ControlToValidate="txtname" ErrorMessage="Please , Enter  Name."></asp:RequiredFieldValidator>
                    </EditItemTemplate>
                    <FooterTemplate>
                        <asp:TextBox ID="txtfname" runat="server"></asp:TextBox>
                        <asp:RequiredFieldValidator ID="rfvFooteitynme" ValidationGroup="Footer" runat="server"
                            SetFocusOnError="true" Display="Dynamic" ControlToValidate="txtfname" ErrorMessage="Please , Enter City Name."></asp:RequiredFieldValidator>
                    </FooterTemplate>
                    <FooterStyle Width="200px"></FooterStyle>
                    <ItemStyle Width="200px"></ItemStyle>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Email" SortExpression="E_id" FooterStyle-Width="200px"
                    ItemStyle-Width="200px">
                    <ItemTemplate>
                        <asp:Label ID="lbleid" runat="server" Text='<%# Eval("E_id") %>'></asp:Label>
                    </ItemTemplate>
                    <EditItemTemplate>
                        <asp:TextBox ID="txtemail" runat="server"  Text='<%# Eval("E_id") %>' ></asp:TextBox>
                        <asp:RequiredFieldValidator ID="rfvEditCityname" ValidationGroup="Edit" runat="server"
                            SetFocusOnError="true" Display="Dynamic" ControlToValidate="txtemail" ErrorMessage="Please , Enter Email Id"></asp:RequiredFieldValidator>
                    </EditItemTemplate>
                    <FooterTemplate>
                        <asp:TextBox ID="txtfemail" runat="server"></asp:TextBox>
                        <asp:RequiredFieldValidator ID="vFooterCityname" ValidationGroup="Footer" runat="server"
                            SetFocusOnError="true" Display="Dynamic" ControlToValidate="txtfemail" ErrorMessage="Please , Enter Email Id"></asp:RequiredFieldValidator>
                    </FooterTemplate>
                    <FooterStyle Width="200px"></FooterStyle>
                    <ItemStyle Width="200px"></ItemStyle>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Mobile" SortExpression="Mobile" FooterStyle-Width="200px"
                    ItemStyle-Width="200px">
                    <ItemTemplate>
                        <asp:Label ID="lblMObile" Text='<%#Bind("mobile")%>' runat="server"></asp:Label>
                    </ItemTemplate>
                    <EditItemTemplate>
                        <asp:TextBox ID="txtEditMObile" Text='<%#Bind("Mobile")%>' runat="server"></asp:TextBox>
                        <asp:RequiredFieldValidator ID="mobilename" ValidationGroup="Edit" runat="server"
                            SetFocusOnError="true" Display="Dynamic" ControlToValidate="txtEditMObile"
                            ErrorMessage="Please , Enter Mobile No."></asp:RequiredFieldValidator>
                    </EditItemTemplate>
                    <FooterTemplate>
                        <asp:TextBox ID="txtmobile" runat="server"></asp:TextBox>
                        <asp:RequiredFieldValidator ID="mobile" ValidationGroup="Footer" runat="server"
                            SetFocusOnError="true" Display="Dynamic" ControlToValidate="txtmobile" ErrorMessage="Please , Enter Mobile NO."></asp:RequiredFieldValidator>
                    </FooterTemplate>
                    <FooterStyle Width="200px"></FooterStyle>
                    <ItemStyle Width="200px"></ItemStyle>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="City Name" SortExpression="City" FooterStyle-Width="200px"
                    ItemStyle-Width="200px">
                    <ItemTemplate>
                        <asp:Label ID="lblCityName" Text='<%#Bind("City")%>' runat="server"></asp:Label>
                    </ItemTemplate>
                    <EditItemTemplate>
                        <asp:TextBox ID="txtEditCityName" Text='<%#Bind("City")%>' runat="server"></asp:TextBox>
                        <asp:RequiredFieldValidator ID="rfvityname" ValidationGroup="Edit" runat="server"
                            SetFocusOnError="true" Display="Dynamic" ControlToValidate="txtEditCityName"
                            ErrorMessage="Please , Enter City Name."></asp:RequiredFieldValidator>
                    </EditItemTemplate>
                    <FooterTemplate>
                        <asp:TextBox ID="txtCityName" runat="server"></asp:TextBox>
                        <asp:RequiredFieldValidator ID="rfvFooterCitame" ValidationGroup="Footer" runat="server"
                            SetFocusOnError="true" Display="Dynamic" ControlToValidate="txtCityName" ErrorMessage="Please , Enter City Name."></asp:RequiredFieldValidator>
                    </FooterTemplate>
                    <FooterStyle Width="200px"></FooterStyle>
                    <ItemStyle Width="200px"></ItemStyle>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Action">
                    <ItemTemplate>
                        <asp:Button ID="btnedit" runat="server" Text="Edit" CommandName="Edit" />
                        <asp:Button ID ="btndelete" runat ="server" Text ="Delete" CommandName ="Delete" />
                    </ItemTemplate>
                    <EditItemTemplate>
                        <asp:Button ID="btnupdate" runat="server" Text="Update" ValidationGroup="Edit" CommandName ="update" />
                        <asp:Button ID="btncancel" runat="server" Text="Cancel" CommandName="Cancel" />
                    </EditItemTemplate>
                    <FooterTemplate>
                        <asp:Button ID="btninsert" runat="server" Text="Insert" ValidationGroup="Footer"
                            OnClick="btninsert_Click" />
                    </FooterTemplate>
                </asp:TemplateField>
            </Columns>
        </asp:GridView>
  


gridsorting.aspx.cs



using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using DAL;
public partial class gridsorting : System.Web.UI.Page
{
    DataTable dt = new DataTable();
    Dal odal = new Dal();
    DataSet ds = new DataSet();
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            fill();
        }
    }
    public void fill()
    {
        dt = odal.selectbyquerydt("select * from registeruser");
        grd.DataSource = dt;
        grd.DataBind();

    }


    public SortDirection dir
    {
        get
        {
            if (ViewState["dirState"] == null)
            {
                ViewState["dirState"] = SortDirection.Ascending;
            }
            return (SortDirection)ViewState["dirState"];
        }
        set
        {
            ViewState["dirState"] = value;
        }

    }

    protected void grd_Sorting(object sender, GridViewSortEventArgs e)
    {
        fill();
        DataTable dt = new DataTable();
        dt = grd.DataSource as DataTable;
        {
            string SortDir = string.Empty;
            if (dir == SortDirection.Ascending)
            {
                dir = SortDirection.Descending;
                SortDir = "Desc";
            }
            else
            {
                dir = SortDirection.Ascending;
                SortDir = "Asc";
            }
            DataView sortedView = new DataView(dt);
            sortedView.Sort = e.SortExpression + " " + SortDir;
            grd.DataSource = sortedView;
            grd.DataBind();
        }
    }
    protected void grd_RowEditing(object sender, GridViewEditEventArgs e)
    {
        grd.EditIndex = 0;

    }
    protected void btninsert_Click(object sender, EventArgs e)
    {
        TextBox Name = (TextBox)grd.FooterRow.FindControl("txtfName") as TextBox;
        TextBox Email = (TextBox)grd.FooterRow.FindControl("txtfemail") as TextBox;
        TextBox city = (TextBox)grd.FooterRow.FindControl("txtCityName") as TextBox;
        TextBox mobile = (TextBox)grd.FooterRow.FindControl("txtmobile") as TextBox;

        odal.updatedata("insert into registeruser (name,e_id,Mobile,city) values('" + Name.Text + "','" + Email.Text + "','" + mobile.Text + "','" + city.Text + "')");
        fill();
    }
    protected void grd_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        grd.EditIndex = e.RowIndex;
        int id = int.Parse(grd.DataKeys[grd.EditIndex].Value.ToString());
        TextBox Name = (TextBox)grd.Rows[e.RowIndex].FindControl("txtName") as TextBox;
        TextBox Email = (TextBox)grd.Rows[e.RowIndex].FindControl("txtemail") as TextBox;
        TextBox city = (TextBox)grd.Rows[e.RowIndex].FindControl("txtEditCityName") as TextBox;
        TextBox mobile = (TextBox)grd.Rows[e.RowIndex].FindControl("txtEditMObile") as TextBox;
        string str = "update registeruser set name='" + Name.Text + "',e_id='" + Email.Text + "',city='" + city.Text + "',mobile='" + mobile.Text + "' where id =" + id;
        odal.updatedata(str);
        grd.EditIndex = -1;
        fill();
    }
    protected void grd_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {
        grd.EditIndex = -1;
        fill();
    }
    protected void grd_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        grd.PageIndex = e.NewPageIndex;
        fill();
    }
 protected void grd_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        grd.EditIndex = e.RowIndex;
        int id = int.Parse(grd.DataKeys[grd.EditIndex].Value.ToString ());
        odal.updatedata("delete from registeruser where id="+id);
        fill();
    }
}

at 1 comment:

Jquery EXAMPLE

============
Design - jquerydemo.aspx
============
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="SimpleDemo.aspx.cs" Inherits="SimpleDemo" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js"></script>
    <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.1/jquery-ui.min.js"></script>
    <script type="text/javascript">
        $(document).ready(function () {
            $('#<%=btnsub.ClientID %>').click(function () {
                $.ajax({
                    type: 'POST',
                    contentType: "application/json; charset=utf-8",
                    url: 'SimpleDemo.aspx/IUD',
                    data: "{'Type':'Save','Id':'" + document.getElementById('txtid').value + "','Name':'" + document.getElementById('txtname').value + "', 'Email':'" + document.getElementById('txtemail').value + "','mobile':'" + document.getElementById('txtmobile').value + "','company':'" + document.getElementById('txtcompany').value + "','salary':'" + document.getElementById('txtsalary').value + "'}",
                    async: false,
                    success: function (response) {
                        $('#<%=txtid.ClientID %>').val('');
                        $('#<%=txtname.ClientID %>').val('');
                        $('#<%=txtemail.ClientID%>').val('');
                        $('#<%=txtmobile.ClientID %>').val('');
                        $('#<%=txtcompany.ClientID%>').val('');
                        $('#<%=txtsalary.ClientID%>').val('');
                        alert("Record Has been Saved in Database");
                    },
                    error: function () {
                        console.log('there is some error');
                    }

                });

            });

        });
     
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <fieldset>
            <legend>Insert</legend>
            <table>
                <tr>
                    <td>
                        Id:
                    </td>
                    <td>
                        <asp:TextBox ID="txtid" runat="server" ClientIDMode="Static"></asp:TextBox>
                    </td>
                </tr>
                <tr>
                    <td>
                        Name:
                    </td>
                    <td>
                        <asp:TextBox ID="txtname" runat="server" ClientIDMode="Static"></asp:TextBox>
                    </td>
                </tr>
                <tr>
                    <td>
                        Email:
                    </td>
                    <td>
                        <asp:TextBox ID="txtemail" runat="server" ClientIDMode="Static"></asp:TextBox>
                    </td>
                </tr>
                <tr>
                    <td>
                        Mobile:
                    </td>
                    <td>
                        <asp:TextBox ID="txtmobile" runat="server" ClientIDMode="Static"></asp:TextBox>
                    </td>
                </tr>
                <tr>
                    <td>
                        Company:
                    </td>
                    <td>
                        <asp:TextBox ID="txtcompany" runat="server" ClientIDMode="Static"></asp:TextBox>
                    </td>
                </tr>
                <tr>
                    <td>
                        Salary:
                    </td>
                    <td>
                        <asp:TextBox ID="txtsalary" runat="server" ClientIDMode="Static"></asp:TextBox>
                    </td>
                </tr>
                <tr>
                    <td>
                        <asp:Button ID="btnsub" runat="server" Text="submit" ClientIDMode="Static" />
                    </td>
                    <td>
                    </td>
                </tr>
                <tr>
                    <td>
                        &nbsp;</td>
                    <td>
                        &nbsp;</td>
                </tr>
            </table>
            <asp:GridView ID="grvEmp" runat="server" AutoGenerateColumns="False"
                CellPadding="4" EnableModelValidation="True" ForeColor="#333333"
                GridLines="None">
                <AlternatingRowStyle BackColor="White" />
                <Columns>          
                    <asp:BoundField HeaderText="ID" DataField="Id" />
                    <asp:BoundField HeaderText="Name" DataField="Name" />
                    <asp:BoundField HeaderText="Email" DataField="Email" />
                    <asp:ButtonField Text="Edit" ButtonType="Button" />
                </Columns>
                <EditRowStyle BackColor="#2461BF" />
                <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
                <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
                <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
                <RowStyle BackColor="#EFF3FB" />
                <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
            </asp:GridView>
        </fieldset>
    </div>
    </form>
</body>
</html>


============
Coding - jquerydemo.aspx.cs
============
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Services;
using System.Data;
public partial class SimpleDemo : System.Web.UI.Page
{
   
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
           
        }
    }

    [WebMethod]
    public static string IUD(string Type, int Id, string Name, string Email, string mobile, string company, int salary)
    {
        Class obj = new Class();
        return obj.iuddata(Type, Id,Name, Email, mobile, company, salary);
    }

    [WebMethod]
    public DataSet Fetch(string Type, int Id)
    {
        Class obj = new Class();
        grvEmp.DataSource = obj.fetchdata(Type, Id);
        grvEmp.DataBind();
        return obj.fetchdata(Type, Id);
    }
}

=================
Class File - class.cs
=================
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
/// <summary>
/// Summary description for Class
/// </summary>
public class Class
{
    SqlConnection con = new SqlConnection(@"Data Source=VISION4-PC;Initial Catalog=JsonData;User ID=sa;Password=vision");
    DataSet ds = new DataSet();
public Class()
{
//
// TODO: Add constructor logic here
//
}

    public string iuddata(string Type,int Id, string Name, string Email, string mobile, string company, int salary)
    {
        con.Open();
        SqlCommand cmd = new SqlCommand("[dbo].[IUDJson]", con);
        cmd.Parameters.AddWithValue("@Type", Type);
        cmd.Parameters.AddWithValue("@Id", Id);
        cmd.Parameters.AddWithValue("@Name", Name);
        cmd.Parameters.AddWithValue("@Email", Email);
        cmd.Parameters.AddWithValue("@mobile", mobile);
        cmd.Parameters.AddWithValue("@company", company);
        cmd.Parameters.AddWithValue("@salary", salary);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.ExecuteNonQuery();
        con.Close();
        return "Record Successfully Done.";
    }

    public DataSet fetchdata(string Type, int Id)
    {
        con.Open();
        SqlCommand cmd = new SqlCommand("[dbo].[IUDJson]", con);
        cmd.Parameters.AddWithValue("@Type", Type);
        cmd.Parameters.AddWithValue("@Id", Id);
        cmd.CommandType = CommandType.StoredProcedure;
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        da.Fill(ds);
        con.Close();
        return ds;
    }
}

=============
DATABASE SCRIPT
=============

USE [JsonData]
GO
/****** Object:  Table [dbo].[DemoDB]    Script Date: 11/14/2014 12:05:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DemoDB](
[Id] [int] NOT NULL,
[Name] [varchar](50) NULL,
[Email] [varchar](50) NULL,
[mobile] [varchar](50) NULL,
[company] [varchar](50) NULL,
[salary] [int] NULL,
 CONSTRAINT [PK_DemoDB] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[DemoDB] ([Id], [Name], [Email], [mobile], [company], [salary]) VALUES (1, N'jalpa', N'hitpatel@gmail.com', N'8794561230', N'vision', 5205)
INSERT [dbo].[DemoDB] ([Id], [Name], [Email], [mobile], [company], [salary]) VALUES (12, N'vision', N'hitpatel@gmail.com', N'7987546410', N'infotech', 450100)
/****** Object:  Table [dbo].[Student]    Script Date: 11/14/2014 12:05:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Student](
[Id] [int] IDENTITY(1,1) NOT NULL,
[name] [varchar](50) NULL,
[e_id] [varchar](50) NULL,
[Mobile] [varchar](50) NULL,
[city] [varchar](50) NULL,
 CONSTRAINT [PK__Student__3214EC077F60ED59] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[Student] ON
INSERT [dbo].[Student] ([Id], [name], [e_id], [Mobile], [city]) VALUES (1, N'Hitesh', N'hitpatel@gmail.com', N'7987894532', N'surat')
INSERT [dbo].[Student] ([Id], [name], [e_id], [Mobile], [city]) VALUES (2, N'Abhay', N'abhay@gmail.com', N'9878456512', N'surat')
SET IDENTITY_INSERT [dbo].[Student] OFF
/****** Object:  StoredProcedure [dbo].[IUDJson]    Script Date: 11/14/2014 12:05:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--exec [dbo].[IUDJson] 'Save',1,'Hitesh','hitpatel@gmail.com','7895461230','vision',12000
CREATE PROCEDURE [dbo].[IUDJson]
--string mode, string name, string email, string mobile, string company, int salary
@Type varchar(15),
@id int=0,
@name varchar(30)='',
@email varchar(30)='',
@mobile varchar(15)='',
@company varchar(30)='',
@salary int=0
AS
BEGIN

if @Type = 'Save'
begin
if Exists (select 1 from dbo.DemoDB where Id=@id)
begin
update dbo.DemoDB set Name=@name,Email=@email,mobile=@mobile,company=@company,salary=@salary
where Id = @id
end
else
begin
Insert into dbo.DemoDB values (@id,@name,@email,@mobile,@company,@salary)

end
end

if @Type = 'Delete'
begin
Delete from dbo.DemoDB where Id=@id
end
END
GO

at No comments:
Subscribe to: Posts (Atom)

Demo for Repository Pattern in ASP.Net

----------------------------------------------------------- ----------------------------------------------------------- Repository Projec...