Simply Code For Multiple Insert : The Official Microsoft ASP.NET Forums

Reply

Simply Code For Multiple Insert

Feb 27, 2012 06:48 AM|LINK

Hi...
I want simple this code with use SqlDataSource1....
And The Code is....

public partial class _Default : System.Web.UI.Page
{
    private void BindGrid()
    {
        List<Customer> items = new List<Customer>(5);
        for (int i = 0; i < 5; i++)
        {
            Customer c = new Customer();
            items.Add(c);
        }
        GridView1.DataSource = items;
        GridView1.DataBind();
    }

    SqlConnection cnn = new SqlConnection("data source=.;initial catalog=northwind;user id=sa;password=sa");
    SqlCommand cmd = new SqlCommand();

    private void BeginAdd()
    {
        cnn.Open();
        SqlTransaction tran= cnn.BeginTransaction();
        cmd.Connection = cnn;
        cmd.Transaction = tran;
        cmd.CommandText = "insert into customers(customerid,companyname,contactname,country) values(@custid,@company,@contact,@country)";
        SqlParameter p1 = new SqlParameter("@custid",SqlDbType.VarChar);
        SqlParameter p2 = new SqlParameter("@company", SqlDbType.VarChar);
        SqlParameter p3 = new SqlParameter("@contact", SqlDbType.VarChar);
        SqlParameter p4 = new SqlParameter("@country", SqlDbType.VarChar);
        cmd.Parameters.Add(p1);
        cmd.Parameters.Add(p2);
        cmd.Parameters.Add(p3);
        cmd.Parameters.Add(p4);
    }

    private void CompleteAdd()
    {
        try
        {
            cmd.Transaction.Commit();
            Label1.Text = "Customers added successfully!";
        }
        catch(Exception ex)
        {
            Label1.Text = "Error completing the operation!";
        }
        finally
        {
            cnn.Close();
        }
    }

    private void AddCustomer(string custid, string company, string contact, string country)
    {
        try
        {
            cmd.Parameters[0].Value = custid;
            cmd.Parameters[1].Value = company;
            cmd.Parameters[2].Value = contact;
            cmd.Parameters[3].Value = country;
            cmd.ExecuteNonQuery();
        }
        catch
        {
            cmd.Transaction.Rollback();
        }
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        if(!IsPostBack)
        {
            BindGrid();
        }
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        BeginAdd();
        foreach (GridViewRow row in GridView1.Rows)
        {
            if (row.RowType == DataControlRowType.DataRow)
            {
                string custid = ((TextBox)row.FindControl("TextBox1")).Text;
                string company = ((TextBox)row.FindControl("TextBox2")).Text;
                string contact = ((TextBox)row.FindControl("TextBox3")).Text;
                string country = ((TextBox)row.FindControl("TextBox4")).Text;
                if (custid != "")
                {
                    AddCustomer(custid, company, contact, country);
                }
            }
        }
        CompleteAdd();
    }
    protected void Button2_Click(object sender, EventArgs e)
    {
        BindGrid();
    }
}

I Hope u all Can help me....
Please....

Reply

Re: Simply Code For Multiple Insert

Feb 27, 2012 06:55 AM|LINK

hi,

please provide the markup code also (the sqldatasource markup)

http://www.sambeauvois.be

MCC 2011 award

Reply

Re: Simply Code For Multiple Insert

Feb 27, 2012 02:20 PM|LINK

Like this.....

<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="4"
            ForeColor="#333333" GridLines="None">
            <FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
            <Columns>
                <asp:TemplateField HeaderText="Customer ID">
                    <ItemTemplate>
                        <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("CustomerID") %>' Width="65px"></asp:TextBox>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Company Name">
                    <ItemTemplate>
                        <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("CompanyName") %>'></asp:TextBox>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Contact Name">
                    <ItemTemplate>
                        <asp:TextBox ID="TextBox3" runat="server" Text='<%# Bind("ContactName") %>'></asp:TextBox>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Country">
                    <ItemTemplate>
                        <asp:TextBox ID="TextBox4" runat="server" Text='<%# Bind("Country") %>' Width="82px"></asp:TextBox>
                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
            <RowStyle BackColor="#FFFBD6" ForeColor="#333333" />
            <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
            <PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />
            <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
            <AlternatingRowStyle BackColor="White" />
        </asp:GridView>
    
    </div>
        <asp:Button ID="Button1" runat="server" Text="Save All" OnClick="Button1_Click" />
        <asp:Button ID="Button2" runat="server" OnClick="Button2_Click" Text="Clear Grid" />
    <br />
    <asp:SqlDataSource ID="SqlDataSource1" runat="server"
        ConflictDetection="CompareAllValues"
        ConnectionString="<%$ ConnectionStrings:PelanganConnectionString %>"
        DeleteCommand="DELETE FROM [Biodata] WHERE [CustomerID] = @original_CustomerID AND (([CompanyName] = @original_CompanyName) OR ([CompanyName] IS NULL AND @original_CompanyName IS NULL)) AND (([ContactName] = @original_ContactName) OR ([ContactName] IS NULL AND @original_ContactName IS NULL)) AND (([Country] = @original_Country) OR ([Country] IS NULL AND @original_Country IS NULL))"
        InsertCommand="INSERT INTO [Biodata] ([CompanyName], [ContactName], [Country]) VALUES (@CompanyName, @ContactName, @Country)"
        OldValuesParameterFormatString="original_{0}"
        SelectCommand="SELECT * FROM [Biodata]"
        UpdateCommand="UPDATE [Biodata] SET [CompanyName] = @CompanyName, [ContactName] = @ContactName, [Country] = @Country WHERE [CustomerID] = @original_CustomerID AND (([CompanyName] = @original_CompanyName) OR ([CompanyName] IS NULL AND @original_CompanyName IS NULL)) AND (([ContactName] = @original_ContactName) OR ([ContactName] IS NULL AND @original_ContactName IS NULL)) AND (([Country] = @original_Country) OR ([Country] IS NULL AND @original_Country IS NULL))">
        <DeleteParameters>
            <asp:Parameter Name="original_CustomerID" Type="Int64" />
            <asp:Parameter Name="original_CompanyName" Type="String" />
            <asp:Parameter Name="original_ContactName" Type="String" />
            <asp:Parameter Name="original_Country" Type="String" />
        </DeleteParameters>
        <InsertParameters>
            <asp:Parameter Name="CompanyName" Type="String" />
            <asp:Parameter Name="ContactName" Type="String" />
            <asp:Parameter Name="Country" Type="String" />
        </InsertParameters>
        <UpdateParameters>
            <asp:Parameter Name="CompanyName" Type="String" />
            <asp:Parameter Name="ContactName" Type="String" />
            <asp:Parameter Name="Country" Type="String" />
            <asp:Parameter Name="original_CustomerID" Type="Int64" />
            <asp:Parameter Name="original_CompanyName" Type="String" />
            <asp:Parameter Name="original_ContactName" Type="String" />
            <asp:Parameter Name="original_Country" Type="String" />
        </UpdateParameters>
    </asp:SqlDataSource>
    <br />
        <br />
        <asp:Label ID="Label1" runat="server" EnableViewState="False" Font-Bold="True" ForeColor="Red"></asp:Label>
    </form>
</body>

Reply

Re: Simply Code For Multiple Insert

Feb 29, 2012 12:21 AM|LINK

It seems that you don't need a DataBase but just a List<Custom> to play with GridView，and what you want is to achieve the effect of GridView+SqlDataSource……

If so，I suggest you trying to have a look at ObjectDataSource instead of SqlDataSource，for ObjectDataSource is used for object programming like your situation，and it's a generic solution；compared with this，SqlDataSource is used for sql database。

Solution sample：http://www.codeproject.com/Articles/13524/ObjectDataSource-In-Depth

Reguards！