asp.net 如何把Table里的数据一次性插入数据库中
问题描述：
table有两列 数据是在前端添加的所以行数不确定
希望的效果是：
按保存按钮 将textbox1和table里两列x行的数据分别插入表1和表2中
前台已经做好了 求后台button-Click ()的具体代码 能用立马采纳 谢谢
答
https://ask.csdn.net/questions/688319
下面内容直接保存为x.aspx进行测试，x.aspx，不要改动，如果改了文件名注意修改ajax请求的页面名称
<%@ Page Language="C#" AutoEventWireup="true" %>
<script runat="server">
protected void Page_Load(object sender, EventArgs args)
{
if (Request.Form["op"] == "save")
{
string data = Request.Form["data"], sql = "";
if (!string.IsNullOrEmpty(data))
{
string[] arr = data.Split('\n'), item;
foreach (string s in arr)
{
item = s.Split('|');
if (item.Length < 2) continue;
sql += "insert into xxxtable(name,num)values('" + item[0] + "'," + item[1] + ")";
}
if (sql != "")
{ //执行sql语句，不用我多少了吧。。。
Response.Write("1");//成功输出1
}
else Response.Write("数据错误...");
}
Response.End();
}
}
</script>
<script src="https://cdn.bootcss.com/jquery/1.7.1/jquery.min.js"></script>
货品名：<input type="text" id="text1" /> 数量：<input type="text" id="text2" /><input type="button" value="添加" /><br />
<table id="table" border="1"><tr><td>货品名</td><td>数量</td></tr></table>
<br /><a onclick="save()">保存</a>
<script>
$(function () {
$(':button').click(function (e) {
var text1 = $('#text1').val();
var text2 = $('#text2').val();
$('#table').append('<tr><td>' + text1 + '</td>><td>' + text2 + '</td></tr>')
})
});
function save() {
var data = $('#table tr:gt(0)').map(function () {
//如果输入内容存在英文状态下|则替换为全角状态下的｜
return $('td', this).map(function () { return this.innerHTML.replace(/\|/g, '｜') }).get().join('|');//每组用英文状态下|分隔
}).get().join('\n');//每行数据用换行符隔开
if (!data) { alert('先添加数据！'); return }
$.ajax({
type: 'POST', url: 'x.aspx', data: { data: data, op: 'save' }, complete: function (xhr) {
var s = xhr.responseText;
if (s == '1') alert('保存成功')
else alert(s)
}
})
}
</script>
答
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class affair_releaseAffair : System.Web.UI.Page
{
Sqlhelper sp = null;
SqlDataReader dr = null;
SqlCommand comm = null;
protected void Page_Load(object sender, EventArgs e)
{
if (Session["username"] != null)
{
if (ViewState["count"] != null)
{
//每次刷新都重新建立表格循环再次添加行
for (int i = 0; i < Convert.ToInt16(ViewState["count"])/2; i++)
{
AddRows(tableTarget);
}
}
}
else {
Response.Write("<script>alert('请先登录！')</script>");
// Server.Transfer("~/login.aspx");
Response.Write("<script>top.window.location.href = '../login.aspx?r='+Math.random() ;</script>");
//Response.Write("<script>window.location='../login.aspx';</script>");
//Response.Redirect("~/login.aspx",true);
}
}
// 删除按钮事件，总是默认删除表的最后一行
protected void btnDelRow_Click(object sender, EventArgs e)
{
tableTarget.Rows.RemoveAt(tableTarget.Rows.Count - 1);
tableTarget.Rows.RemoveAt(tableTarget.Rows.Count - 1);
ViewState["count"] = Convert.ToInt16(ViewState["count"]) - 2;
}
protected void btnAddRow_Click(object sender, EventArgs e)
{
AddRows(tableTarget);
ViewState["count"] = Convert.ToInt16(ViewState["count"]) + 2; //记录的table行数。
}
/// <summary>
///table中有8列，四列label，三列是TextBox一列是DropDownList，这是一个添加行的函数
///给表table1添加两行
/// </summary>
/// <param name="table"></param>
public void AddRows(Table table) {
TableRow tr0 = new TableRow();
TableCell tc = new TableCell();
Label lb0 = new Label();
lb0.Text = "指标内容:";
lb0.Width = Unit.Parse("80px");
tc.Controls.Add(lb0); tc.Width = Unit.Parse("80px");
TableCell tc0 = new TableCell();
TextBox tb0 = new TextBox();
tb0.ID = "tbc" + table.Rows.Count; //content指标内容
tb0.TextMode = TextBoxMode.MultiLine; //多行
tb0.Width = Unit.Parse("400px");
tb0.Height = Unit.Parse("50px");
tc0.Controls.Add(tb0);
tc0.ColumnSpan = 7;
tr0.Cells.Add(tc);
tr0.Cells.Add(tc0);
table.Rows.Add(tr0);
TableRow tr = new TableRow();
for (int i = 1; i <= 3; i++) {
TableCell tc1 = new TableCell();
Label lb1 = new Label();
lb1.Text = "指标"+i+":";
lb1.Width= Unit.Parse("50px");
tc1.Controls.Add(lb1); tc1.Width = Unit.Parse("50px");
TableCell tc2 = new TableCell();
TextBox tb1 = new TextBox();
tb1.ID = "tb"+i+""+ table.Rows.Count;
tb1.TextMode = TextBoxMode.MultiLine;
tb1.Width = Unit.Parse("300px");
tc2.Controls.Add(tb1);
tr.Cells.Add(tc1);
tr.Cells.Add(tc2);
}
TableCell tc7 = new TableCell();
Label lb4 = new Label(); lb4.Width = Unit.Parse("80px");
lb4.Text = "您的选择:";
tc7.Controls.Add(lb4); tc7.Width = Unit.Parse("80px");
TableCell tc8 = new TableCell();
DropDownList dpl = new DropDownList();
dpl.ID = "dpl" + table.Rows.Count;
for (int i = 1; i < 4; i++) dpl.Items.Add(i.ToString());
tc8.Controls.Add(dpl);
tr.Cells.Add(tc7);
tr.Cells.Add(tc8);
table.Rows.Add(tr);
table.Attributes.Add("border", "1");
}
//保存选中的值，保存到数据库
protected void btnSave_Click(object sender, EventArgs e)
{
int num = saveAffair();
if (num == 1)
{//成功
Response.Write("<script>alert('添加成功！')</script>");
}
else {
Response.Write("<script>alert('添加失败！')</script>");
}
}
//保存事务
public int saveAffair() {
String content,affairName,sqlStr;
int affairId=0,userId = getUserId(),num2=0;
SqlDataReader dr = null;
affairName = text_affairName.Text.Trim();
content = text_affairContent.Text.Trim();
if (affairName.Equals("") || content.Equals(""))
{
Response.Write("<script>alert('请输入事务！')</script>");
}
else {
num2 = 1;
sp = Sqlhelper.getSqlhelper();
comm = sp.getComm();
sqlStr = "insert into affair(UserId,Content,AffairName,createDate) values("+ userId + ",'"+ content + "','"+ affairName + "',GETDATE());";
try
{
comm.CommandText = sqlStr;
int num = comm.ExecuteNonQuery();
sqlStr = "select * from affair order by id desc;";
comm.CommandText = sqlStr;
dr = comm.ExecuteReader();
if (dr.HasRows == true)
{
dr.Read();
affairId = Convert.ToInt32(dr["Id"].ToString());
}
dr.Close();
if (affairId > 0)
saveTarget(affairId, comm);//保存指标
}
catch (Exception ee)
{
num2 = 2;
throw;
}
finally {
sp.Close();
}
}//else
return num2;
}
//保存指标
public int saveTarget(int affairId,SqlCommand comm) {
String content="", select1="", select2="", select3="", answer,sqlStr="";
int num=0;
for (int i = 0; i < tableTarget.Rows.Count; i++)
{
if (i % 2 == 0)
{ //content内容
content = ((TextBox)tableTarget.Rows[i].FindControl("tbc" + i)).Text.Trim();
}
else
{//选项
select1 = ((TextBox)tableTarget.Rows[i].FindControl("tb1" + i)).Text.Trim();
select2 = ((TextBox)tableTarget.Rows[i].FindControl("tb2" + i)).Text.Trim();
select3 = ((TextBox)tableTarget.Rows[i].FindControl("tb3" + i)).Text.Trim();
answer = ((DropDownList)tableTarget.Rows[i].FindControl("dpl" + i)).SelectedValue;
if(!content.Equals(""))
sqlStr = "insert into "+"target"+ creatRandom() + " values("+ affairId + ",'"+ content + "','"+ select1 + "','"+
select2 + "','"+ select3 + "','"+ answer + "');";
}
if (!sqlStr.Equals("")|| !select1.Equals("")|| !select2 .Equals("")|| !select3.Equals("")) {
comm.CommandText = sqlStr;
num = comm.ExecuteNonQuery();
}
}
return num;
}
public int creatRandom() {
//第二种方法可以指定一个int型参数作为随机种子：
long tick = DateTime.Now.Ticks;
Random ran = new Random((int)(tick & 0xffffffffL) | (int)(tick >> 32));
// 而下面这段代码则指定返回值必须在50 - 100的范围之内：
int iResult;
int iUp = 20;
int iDown = 1;
iResult = ran.Next(iDown, iUp);
return iResult;
}
public int getUserId() {
int userId=0;
sp = Sqlhelper.getSqlhelper();
comm = sp.getComm();
String username = Session["username"].ToString();
String sqlStr = "select Id from userInfo where Name='"+ username+"'";
comm.CommandText = sqlStr;
try
{
userId = (int)comm.ExecuteScalar();
}
catch (Exception)
{
throw;
}
return userId;
}
}
答
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="releaseAffair.aspx.cs" Inherits="affair_releaseAffair" %>
<!DOCTYPE html>
<!--发布事务页面：名称AffairName，内容content，创建日期-->
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
<title></title>
</head>
<body>
<form id="form1" runat="server">
前台后面的代码，采纳了以后给。
答
循环获取table数据一个个插,每循环一次插一次