Answered by:
SQL connection - resuse a selectstatement and update each row
Question
-
User-60784728 posted
Hi!
I´m having some SQL connections with select statements in code behind. I would like to find a safe och good way to also update the selected rows.
As for today I use a new SQLCommande with the update statement within a FOR Each drv as DatarovWiev. That only makes a update statement running for Each and not actually selecting that particular row, so I must add paramaters to make sure that that row is the only one updated.
Can I somehow use for each row update "this row" SET column2 = "new value".
Best regards Daniel
Wednesday, June 15, 2011 7:04 AM
Answers
-
User-60784728 posted
Dim conn As New SqlConnection("Data Source=SQL;Initial Catalog=TABLE;User Id=sa;Password=PASS;") conn.Open() 'select statement and fill table Dim da As New SqlCommand("select whole_phone_number, agent_input39, agent_input1, agent_input2, agent_input3, agent_input4, agent_input5, BOKEN = ('I ditt välkomstpaket hittar du ' + agent_input26 + ' och ' + agent_input27+'.') from agent_input where agent_input39 like 'srk%' AND convert(varchar,entry_date,102) >= '2011.06.13' AND convert(varchar,entry_date,102) <= GETDATE()-1 and agent_input24 is null and agent_input22 is null ", conn) Dim mySqlDataAdapter As New SqlDataAdapter() mySqlDataAdapter.SelectCommand = da Dim ds As New DataSet mySqlDataAdapter.Fill(ds) Dim dt As DataTable = ds.Tables(0) rptList.DataSource = dt rptList.DataBind() 'Here I want to update each selected (returned row in my selected statement). As for now I´m running a new update statement with a where clause. I want to update the already selected rows. For Each drv As DataRowView In dt.DefaultView Dim up As New SqlCommand("UPDATE agent_input SET agent_input24 = '1' where agent_input39 like 'srk%' AND convert(varchar,entry_date,102) >='2011.06.13' and whole_phone_number= @nummer and agent_input39=@proj AND convert(varchar,entry_date,102) <= GETDATE()-1 and agent_input24 is null and agent_input22 is null ", conn) up.Parameters.Add("@nummer", SqlDbType.VarChar, 20).Value = drv("whole_phone_number") up.Parameters.Add("@proj", SqlDbType.VarChar, 20).Value = drv("agent_input39") up.ExecuteNonQuery() Next If IsPostBack Then rptList.DataSource = dt rptList.DataBind() End If conn.Close()- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Wednesday, June 15, 2011 8:33 AM
All replies
-
User1315229986 posted
Please post your code and explain the above things with it
Wednesday, June 15, 2011 7:11 AM -
User-60784728 posted
Dim conn As New SqlConnection("Data Source=SQL;Initial Catalog=TABLE;User Id=sa;Password=PASS;") conn.Open() 'select statement and fill table Dim da As New SqlCommand("select whole_phone_number, agent_input39, agent_input1, agent_input2, agent_input3, agent_input4, agent_input5, BOKEN = ('I ditt välkomstpaket hittar du ' + agent_input26 + ' och ' + agent_input27+'.') from agent_input where agent_input39 like 'srk%' AND convert(varchar,entry_date,102) >= '2011.06.13' AND convert(varchar,entry_date,102) <= GETDATE()-1 and agent_input24 is null and agent_input22 is null ", conn) Dim mySqlDataAdapter As New SqlDataAdapter() mySqlDataAdapter.SelectCommand = da Dim ds As New DataSet mySqlDataAdapter.Fill(ds) Dim dt As DataTable = ds.Tables(0) rptList.DataSource = dt rptList.DataBind() 'Here I want to update each selected (returned row in my selected statement). As for now I´m running a new update statement with a where clause. I want to update the already selected rows. For Each drv As DataRowView In dt.DefaultView Dim up As New SqlCommand("UPDATE agent_input SET agent_input24 = '1' where agent_input39 like 'srk%' AND convert(varchar,entry_date,102) >='2011.06.13' and whole_phone_number= @nummer and agent_input39=@proj AND convert(varchar,entry_date,102) <= GETDATE()-1 and agent_input24 is null and agent_input22 is null ", conn) up.Parameters.Add("@nummer", SqlDbType.VarChar, 20).Value = drv("whole_phone_number") up.Parameters.Add("@proj", SqlDbType.VarChar, 20).Value = drv("agent_input39") up.ExecuteNonQuery() Next If IsPostBack Then rptList.DataSource = dt rptList.DataBind() End If conn.Close()- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Wednesday, June 15, 2011 8:33 AM -
User3866881 posted
Hello：）
In fact you can use SqlCommandBuilder to automatically generate insert, update and delete commands and the next step that you can do is just to do inserting, deleting and updating in the DataTable, and in the end you can just call "Update" method instead of running SqlCommand again and again.In fact I did a simple demo for you, if you want it, please send me email at maledong@qq.com, with the title——Batch Excel-Like GridView Inserting, Deleting and Updating Wanted.
Here's the codes——
【aspx】
<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="Default.aspx.vb" Inherits="VBASPNETExcelLikeGridView._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 id="Head1" runat="server">
<title>VBExcelLikeGridView</title>
<script src="http://ajax.microsoft.com/ajax/jquery/jquery-1.4.4.min.js" type="text/javascript">
</script>
<script type="text/javascript">//Function to read HidState (JSON) to keep the state color
function ResetColors(color1, color2) {
var contents = $(":hidden:last").val();
// If not "[]", meaning something changed, reset colors.
if (contents.toString() != "[]") {
// Convert to JSON object.
var objectc = eval(contents);// To reset colors depending on whether it's changed
for (var i = 0; i < objectc.length; ++i) {
if (objectc[i].Color != '') {
$("td:eq(" + objectc[i].Index + ")").css("background-color", objectc[i].Color);
}var checked = objectc[i].Deleted == "True";
if (parseInt(objectc[i].Index) % 2 == 0) {
$("td:eq(" + parseInt(objectc[i].Index - 2) + ")").find(":checkbox").attr("checked", checked);
}
else {
$("td:eq(" + parseInt(objectc[i].Index - 3) + ")").find(":checkbox").attr("checked", checked);
}// If a checkbox checked, change the row color
if (checked) {
$("td:eq(" + objectc[i].Index + ")").parent().css("background-color", "red");
}
}
}}
//Validation for Save
function SaveValidate() {
//first tell whether you've missed "Name" to be ful-filled...if (Page_ClientValidate('Fill')) {
return confirm('Do you really want to save all these changes together?');
}
else {
alert("Attention! You cannot leave a name blank!");
}
}//Validation for Insert
function InsertValidate() {
//first tell whether you've missed "Name" to be ful-filled...if (!Page_ClientValidate('Insert')) {
alert("Attention! You cannot insert a blank name!");
}
}// Add dynamically events for all textboxes
// except the footer one to turn the background color.
function AddEvents() {var rowarray = $("tr");
for (var i = 0; i < rowarray.length - 1; ++i) {
$(rowarray[i]).find(":text").change(function () {
$(this).parent().css("background", "blue");
});
}}
$(function () {
//Keep the original color row for odd
var color1 = $("tr:eq(1)").css("background-color").valueOf();
var color2 = $("tr:eq(2)").css("background-color").valueOf();
var headercolor = $("tr:first").css("background-color").valueOf();
var footercolor = $("tr:last").css("background-color").valueOf();AddEvents();
// Header checkbox's cascading effect:
$("#chkAll").click(function () {$(":checkbox").attr("checked", $(this).attr("checked"));
if ($(this).attr("checked")) {
$(":checkbox").parent().parent().css("background-color", "red");
//Reset the color of header
$("tr:first").css("background-color", headercolor);
}
else {
$("tr:odd").css("background-color", color1);
$("tr:even").css("background-color", color2);//Reset the color of header and footer
$("tr:first").css("background-color", headercolor);
$("tr:last").css("background-color", footercolor);
}
});//Single checkbox checked event
$(":checkbox").click(function () {
if ($(this).attr("checked")) {
$(this).parent().parent().css("background-color", "red");
}
else {
if ($(this).parent().parent().index() % 2 == 0) {
$(this).parent().parent().css("background-color", color2);
}
else {
$(this).parent().parent().css("background-color", color1);
}
}//Reset the header color
$("tr:first").css("background-color", headercolor);
});ResetColors(color1, color2);
})</script>
</head>
<body>
<form id="form1" runat="server">
<h1>
Demo for Batching Actions</h1>
<span style="color: Red">red row to be deleted</span>
<br />
<span style="color: green">green row to be added</span>
<br />
<span style="color: blue">blue cell to be modified</span>
<br />
<hr />
<div>
<asp:GridView ID="GridView1" runat="server" Width="70%" Height="50%" AutoGenerateColumns="False"
CellPadding="4" ForeColor="#333333" GridLines="None" ShowFooter="True">
<AlternatingRowStyle BackColor="White" />
<Columns>
<asp:TemplateField HeaderText="Delete State">
<HeaderTemplate>
<input id="chkAll" type="checkbox" />
Delete
</HeaderTemplate>
<ItemTemplate>
<asp:CheckBox ID="chkDelete" runat="server" />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Id">
<ItemTemplate>
<%#Eval("Id") %>
</ItemTemplate>
<FooterTemplate>
Name:<asp:TextBox ID="tbNewName" runat="server"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ControlToValidate="tbNewName"
ErrorMessage="You cannot insert a balnk name!" ForeColor="#FFFF66" ValidationGroup="Insert"></asp:RequiredFieldValidator>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Name">
<ItemTemplate>
<asp:TextBox ID="tbName" runat="server" Text='<%#Eval("PersonName") %>'>
</asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="tbName"
ErrorMessage="You cannot leave a name blank!" ValidationGroup="Fill"></asp:RequiredFieldValidator>
</ItemTemplate>
<FooterTemplate>
Address:<asp:TextBox ID="tbNewAddress" runat="server"></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Address">
<ItemTemplate>
<asp:TextBox ID="tbAddress" runat="server" Text='<%#Eval("PersonAddress") %>'>
</asp:TextBox>
</ItemTemplate>
<FooterTemplate>
<asp:Button ID="btnAdd" runat="server" Text="Add a new row" OnClick="btnAdd_Click"
ValidationGroup="Insert" OnClientClick="InsertValidate()" />
</FooterTemplate>
</asp:TemplateField>
</Columns>
<FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" HorizontalAlign="Center"
VerticalAlign="Middle" />
<HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" HorizontalAlign="Center"
VerticalAlign="Middle" />
<PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />
<RowStyle BackColor="#FFFBD6" ForeColor="#333333" HorizontalAlign="Center" VerticalAlign="Middle" />
<SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
</asp:GridView>
</div>
<asp:Button ID="btnSaveAll" runat="server" Height="30px" Text="Save All Changes"
Width="149px" OnClick="btnSaveAll_Click" OnClientClick="SaveValidate()" ValidationGroup="Fill" />
<asp:HiddenField ID="HidState" runat="server" Value="[]" />
</form>
</body>
</html>【cs codes】
'***************************** Module Header ******************************\
'* Module Name: DBProcess.vb
'* Project: CSExcelLikeGridView
'* Copyright (c) Microsoft Corporation
'*
'* This is a UI module which lets which lets you do a batch inserting, updating
'* as well as deleting.
'*
'* This source is subject to the Microsoft Public License.
'* See http://www.microsoft.com/opensource/licenses.mspx#Ms-PL.
'* All other rights reserved.
'\****************************************************************************
Imports System.Web.UI.WebControls
Imports System.Data
Imports System.TextPartial Public Class _Default
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
If Not IsPostBack Then
Dim p As New DBProcess()
Dim dt As DataTable = p.GetDataTable()
ViewState("dt") = dt
GridView1.DataSource = dt
GridView1.DataBind()
End If
End Sub''' <summary>
''' This function will confirm the last modification
''' and do a batch save.
''' </summary>
Protected Sub btnSaveAll_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnSaveAll.Click'Default the value is false, meaning that the db isn't saved
Dim flag As Boolean = FalseDim p As New DBProcess()
Dim dt As DataTable = CType(ViewState("dt"), DataTable)' Change states and do a batch update
For i As Integer = 0 To GridView1.Rows.Count - 1
If TryCast(GridView1.Rows(i).FindControl("chkDelete"), CheckBox).Checked Then
dt.Rows(i).Delete()
flag = True
Else
If dt.Rows(i)("PersonName").ToString() <> TryCast(GridView1.Rows(i).FindControl("tbName"), TextBox).Text Then
If dt.Rows(i).RowState = DataRowState.Unchanged Then
dt.Rows(i).BeginEdit()
End If
dt.Rows(i)("PersonName") = TryCast(GridView1.Rows(i).FindControl("tbName"), TextBox).Text
If dt.Rows(i).RowState = DataRowState.Unchanged Then
dt.Rows(i).EndEdit()
End If
flag = True
End If
If dt.Rows(i)("PersonAddress").ToString() <> TryCast(GridView1.Rows(i).FindControl("tbAddress"), TextBox).Text Then
If dt.Rows(i).RowState = DataRowState.Unchanged Then
dt.Rows(i).BeginEdit()
End If
dt.Rows(i)("PersonAddress") = TryCast(GridView1.Rows(i).FindControl("tbAddress"), TextBox).Text
If dt.Rows(i).RowState = DataRowState.Unchanged Then
dt.Rows(i).EndEdit()
End If
flag = True
End If
End If
Nextp.BatchSave(dt)
' Save data into db, so no need to maintain the state colors
HidState.Value = "[]"dt = p.GetDataTable()
GridView1.DataSource = dt
GridView1.DataBind()If (flag) Then
ClientScript.RegisterStartupScript([GetType](), "js", "alert('Save All Changes successfully!');", True)
End IfEnd Sub
''' <summary>
''' This function processes with the following aspect:
''' Record every row and every cell's state into HidState.
''' </summary>
Protected Sub btnAdd_Click(ByVal sender As Object, ByVal e As EventArgs)
Dim i As Integer = 0
Dim p As New DBProcess()
Dim dt As DataTable = CType(ViewState("dt"), DataTable)
Dim tb1 As TextBox = Nothing
Dim tb2 As TextBox = Nothing
Dim sbu As New StringBuilder()
Dim chkbox As CheckBox = Nothing' To maintain the JSON state.
sbu.Append("[")While i < dt.Rows.Count
' Loop the cells
For j As Integer = 2 To GridView1.HeaderRow.Cells.Count - 1
tb1 = TryCast(GridView1.Rows(i).FindControl("tbName"), TextBox)
tb2 = TryCast(GridView1.Rows(i).FindControl("tbAddress"), TextBox)sbu.Append("{'Index':'" & (i * GridView1.HeaderRow.Cells.Count + j))
'Not added, meaning unchanged or added
If dt.Rows(i).RowState <> DataRowState.Added Then
' Decide whether the 1st cell in db changed or not
If j = 2 Then
If Not tb1.Text.Equals(dt.Rows(i)(j - 1, DataRowVersion.Original)) Then
dt.Rows(i).BeginEdit()
sbu.Append("','Color':'blue',")
dt.Rows(i)(j - 1) = tb1.Text
Else
sbu.Append("','Color':'',")
End If
Else
' Decide whether the 2nd cell in db changed or not
If Not tb2.Text.Equals(dt.Rows(i)(j - 1, DataRowVersion.Original)) Then
dt.Rows(i).BeginEdit()
sbu.Append("','Color':'blue',")
dt.Rows(i)(j - 1) = tb2.Text
Else
sbu.Append("','Color':'',")
End If
End If
dt.Rows(i).EndEdit()
Else' Add row should be marked as green
If dt.Rows(i).RowState = DataRowState.Added Then
sbu.Append("','Color':'green',")
Else
' Other rows should keep the original color
sbu.Append("','Color':'',")
End If
End If' Keep the Delete Statement
chkbox = TryCast(GridView1.Rows(i).FindControl("chkDelete"), CheckBox)
sbu.Append("'Deleted':'" & chkbox.Checked & "'},")
Next
i += 1
End WhileDim r As DataRow = dt.NewRow()
r("PersonName") = TryCast(GridView1.FooterRow.FindControl("tbNewName"), TextBox).Text
r("PersonAddress") = TryCast(GridView1.FooterRow.FindControl("tbNewAddress"), TextBox).Text
dt.Rows.Add(r)
sbu.Append("{'Index':'" & (i * GridView1.HeaderRow.Cells.Count + 2) & "','Color':'green','Deleted':'false'},")
sbu.Append("{'Index':'" & (i * GridView1.HeaderRow.Cells.Count + 3) & "','Color':'green','Deleted':'false'}")
sbu.Append("]")
HidState.Value = sbu.ToString()
GridView1.DataSource = dt
GridView1.DataBind()
End Sub
End Class'***************************** Module Header ******************************\
'* Module Name: DBProcess.vb
'* Project: VBASPNETExcelLikeGridView
'* Copyright (c) Microsoft Corporation
'*
'* This module is managing the Connection,adapter as well as datatable
'* instances.
'*
'* This source is subject to the Microsoft Public License.
'* See http://www.microsoft.com/opensource/licenses.mspx#Ms-PL.
'* All other rights reserved.
'\****************************************************************************
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Data
Imports System.IO
Imports System.Runtime.Serialization.Formatters.Binary''' <summary>
''' This is a class that manages the whole db's connection, create a
''' memory-based datatable, maintaince the state and do a batch save
''' things together in different functions.
''' </summary>
Public NotInheritable Class DBProcess
Private Shared conn As SqlConnection = Nothing
Private Shared adapter As SqlDataAdapter = Nothing
Private dt As DataTable = Nothing''' <summary>
''' This static constructor will read out the whole connect string
''' from defined web.config. The connection and adapter are both
''' pointing to the same db, so only create once.
''' </summary>
Shared Sub New()
Dim constr As String = ConfigurationManager.ConnectionStrings("MyConn").ConnectionString
conn = New SqlConnection(constr)
Dim command As String = "select * from tb_personInfo"
adapter = New SqlDataAdapter(command, conn)
Dim builder As New SqlCommandBuilder(adapter)
builder.GetDeleteCommand(True)
builder.GetInsertCommand(True)
builder.GetUpdateCommand(True)
End Sub''' <summary>
''' This function will create a datatable to reload all the data
''' from the db.
''' </summary>
Public Function GetDataTable() As DataTable
dt = New DataTable()
adapter.Fill(dt)
dt.Columns(0).AutoIncrement = True
dt.Columns(0).AutoIncrementStep = 1
dt.Columns(0).AutoIncrementSeed = dt.Rows.Count
Return dt
End Function''' <summary>
''' Update the DataTable and delete the serialized file.
''' </summary>
Public Sub BatchSave(ByVal dt As DataTable)
adapter.Update(dt)
End Sub
End ClassThursday, June 16, 2011 10:37 PM
