Problem statement: If we want to insert multiple records in sql
table (if we click on 20, then 20 entries will be done)in BooksDetails table.
Means:
- we’ll insert multiple records here in the table as it is shown in this
problem that when we will increase the quantity of records then equal amount of
query will generate simultaneously and executes in the table that creates more
traffic that’s not acceptable .But this type of questions may be asked by
students.
Solution:
First
of all we can create table in SQL i.e BookDetails. In BookDetails table there
are 8 columns.
And in
ASP.Net we create web form.
Create table
BooksDetails
(
SrNo int identity(1,1),
BookType
varchar(50),
BookId as BookType + cast(SrNo as varchar(10)),
BookName
varchar(200),
Publisher
varchar(200),
WriterName
varchar(50),
Price
float,
BarCode
varchar(50)
)
Code
for Default.aspx ……………………………
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div align="center">
<table align="center" border="1">
<tr><td>Name</td><td><asp:TextBox ID="Txtname" runat="server"></asp:TextBox></td></tr>
<tr><td>BookType</td><td><asp:TextBox ID="txtBookType" runat="server"></asp:TextBox></td></tr>
<tr><td>Writer</td><td><asp:TextBox ID="Txtwriter" runat="server"></asp:TextBox></td></tr>
<tr><td>Price</td><td><asp:TextBox ID="txtprice" runat="server"></asp:TextBox></td></tr>
<tr><td>Publisher</td><td><asp:TextBox ID="Txtpubli" runat="server"></asp:TextBox></td></tr>
<tr><td>Barcode</td><td><asp:TextBox ID="Txtbar" runat="server"></asp:TextBox></td></tr>
<tr><td>Quatity</td><td><asp:TextBox ID="txtquanty" runat="server"></asp:TextBox></td></tr>
<tr><td colspan="2"><asp:Button ID="Btnsubmit" runat="server" Text="Save" OnClick="Btnsubmit_Click" /><asp:Label ID="lblmessage" runat="server" Visible="false" Text="Label"></asp:Label></td></tr>
1
</table>
</div>
</form>
</body>
</html>
Code
for Default.aspx.cs ……………………………
using System;
using System.Data.SqlClient;
using System.Configuration;
using System.Drawing;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["dbcon"].ConnectionString);
string res=null;
protected void Btnsubmit_Click(object sender, EventArgs e)
{
int qu = Convert.ToInt32(txtquanty.Text);
res = "insert into BooksDetails values";
for (int i = 1; i <= qu; i++)
{
// res += "insert into BooksDetails
values('"+txtBookType.Text+"','"+Txtname.Text+"','"+Txtpubli.Text+"','"+Txtwriter.Text+"',"+Convert.ToDouble(txtprice.Text)+",'"+Txtbar.Text+"')"
+ "\n";
res += "('" + txtBookType.Text + "','" +
Txtname.Text + "','" + Txtpubli.Text + "','" +
Txtwriter.Text + "'," + Convert.ToDouble(txtprice.Text)
+ ",'" + Txtbar.Text + "')" + ",";
}
SqlCommand cmd = new SqlCommand(res.Remove(res.Length
- 1), con);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
lblmessage.Visible = true;
lblmessage.ForeColor = Color.Green;
lblmessage.Text = "Books Saved Successfully";
}
}
// we are inserting multiple rows in a table. When we insert rows, too much data is uploading and leads to heavy traffic. This is not the correct way to insert multiple records at a time in a table.
So, here is a second method of inserting multiple
records in a table that is stored procedure.
Her we are creating stored procedures in SQL.
-- **************** stored procedures
**********************
create proc
MultipleBooksIns
@BookType varchar(50),
@BookName varchar(200),
@Publisher varchar(200),
@WriterName varchar(50),
@Price float,
@BarCode varchar(50),
@Quantity int
as
begin
declare @Counter int
set @Counter=0
While(@Counter<@Quantity)
Begin
insert
into BooksDetails values(@BookType,@BookName,@Publisher,@WriterName,@Price,@BarCode)
set
@Counter=@Counter+1
End
end
-- **************** end **********************
//Coding for multiple records in table.We create SQLConnection
in Web.Config file and call stored procedure in our Code. Through this way less
traffic occurs and integrity will be maintained .It reduces complexity also.
using System;
using System.Data.SqlClient;
using System.Configuration;
using System.Drawing;
using System.Data;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["dbcon"].ConnectionString);
string res=null;
protected void Btnsubmit_Click(object sender, EventArgs e)
{
SqlCommand cmd = new SqlCommand("MultipleBooksIns", con);
cmd.Parameters.AddWithValue("@BookType",
txtBookType.Text);
cmd.Parameters.AddWithValue("@BookName",
Txtname.Text);
cmd.Parameters.AddWithValue("@Publisher",
Txtpubli.Text);
cmd.Parameters.AddWithValue("@WriterName",
Txtwriter.Text);
cmd.Parameters.AddWithValue("@Price", Convert.ToDouble(txtprice.Text));
cmd.Parameters.AddWithValue("@BarCode",
Txtbar.Text);
cmd.Parameters.AddWithValue("@Quantity", Convert.ToInt32(txtquanty.Text));
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
lblmessage.Visible = true;
lblmessage.ForeColor = Color.Green;
lblmessage.Text = "Books Saved Successfully";
}
}
Result
Result
0 comments:
Post a Comment