数据库SQL插入难题??? 一个数组Column[n],n是一个整数但未知,如何实现下面的插入语句:Insert 表名 (字段1,字段2,...字段n) (Column[0],Column[1],...Column[n-1]) 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 for循环遍历数据 StringBuilder拼接sql语句 取出数组长度,循环得把字段和value拼接起来! 首先搞清表字段,再确定插入的值对应的字段数,字段——value = OK string[] strs = new string[10]; //你的数组 string[] cols = new string[4]; //列名参数 SqlParameter[] paras = new SqlParameter[strs.Length]; cols[0] = "@col1"; cols[1] = "@col2"; cols[2] = "@col3"; cols[3] = "@col4"; const string cmd = "Insert into tableName(col1, col2, col3, col4) values(@col1, @col2, @col3, @col4)"; //SQL语句 int i = 0; foreach (string str in strs) { paras[i] = new SqlParameter(cols[i], str); } 上面的代码不准确, 只是Demo你得结合自己的情况考虑 可以用Insert into 表名 Select 字段1...字段n,只要保证select中的字段数量与表中的字段数量相同,顺序也相同就好了。 Dictionary<string, string> dic = new Dictionary<string, string>(); dic.Add("column", "a"); dic.Add("column1", "b"); dic.Add("column2", "c"); dic.Add("column3", "d"); string sql = "INSERT INTO TABLE1( "; string param ="VALUES("; foreach (KeyValuePair<string,string> item in dic) { string column = item.Key; string par = item.Value; sql += column + ","; param += par + ","; } sql = sql.Substring(0, sql.Length - 1)+")"; param = param.Substring(0, param.Length - 1) + ")"; string sqlStr = sql + param; private void btSave_Click(object sender, EventArgs e) { //保存新添加的行 try { int ColumnNum = dataGridView1.ColumnCount; int RowNum = dataGridView1.Rows.Count; dataGridView1.AutoGenerateColumns = true; string ColumnValue = null; ;//存放新加入的一行信息序列 string ColumnName = null; ;//加入行的列名 for (int i = 0; i < ColumnNum; i++) { ColumnValue += "'"+dataGridView1.Rows[RowNum - 2].Cells[i].Value+ "',"; ColumnName += dataGridView1.Columns[i].Name + ","; } ColumnValue = ColumnValue.Substring(0, ColumnValue.Length - 1); ColumnName = ColumnName.Substring(0, ColumnName.Length - 1); string strSQL = "insert " + cbxTablesList.Text + " " + "(" + ColumnName + ")" + " values" + "(" + ColumnValue + ")"; CExecuteSQL cex = new CExecuteSQL(); cex.ExecuteSQL(strSQL); MessageBox.Show("恭喜你,保存成功!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception ex) { MessageBox.Show(ex.Message, "警告", MessageBoxButtons.OK, MessageBoxIcon.Information); } }和我的想法一样,不过插入的时候我是按照string类型插入的,竟然没报错,怎么根据数据库表的字段类型插入啊? 这样写会有隐患。如果值里有特殊字符就比较麻烦。建议使用SqlParameter.另外,如果你的n都未知,那么表的列数是否已知? 虽然n未知,但是可以确定n=datagridview.ColumnCount; 请教个改文件名的问题 WF小问题 当我们点“显示桌面”按钮时 程序窗体仍然在桌面上。 问题复杂标题解释不清楚!进来帮忙看看 asp.net反转13位的时间戳? 关于DataGrid输出的问题 用C#调用CMD.exe 如何用C#编写一个全屏浏览器,请大家指点。 [新手求大神指教]怎么讲数据库当中的某一个表的某一列当中的一个数据读取出来并赋值给一个变量 ComboBox增加行?(在winform中是动态增加) .net AppDomain 的问题 winform智能开发,没有WebClient怎么办?
string[] cols = new string[4]; //列名参数
SqlParameter[] paras = new SqlParameter[strs.Length];
cols[0] = "@col1";
cols[1] = "@col2";
cols[2] = "@col3";
cols[3] = "@col4";
const string cmd = "Insert into tableName(col1, col2, col3, col4) values(@col1, @col2, @col3, @col4)"; //SQL语句
int i = 0;
foreach (string str in strs)
{
paras[i] = new SqlParameter(cols[i], str);
}
Dictionary<string, string> dic = new Dictionary<string, string>();
dic.Add("column", "a");
dic.Add("column1", "b");
dic.Add("column2", "c");
dic.Add("column3", "d");
string sql = "INSERT INTO TABLE1( ";
string param ="VALUES(";
foreach (KeyValuePair<string,string> item in dic)
{
string column = item.Key;
string par = item.Value;
sql += column + ",";
param += par + ",";
}
sql = sql.Substring(0, sql.Length - 1)+")";
param = param.Substring(0, param.Length - 1) + ")"; string sqlStr = sql + param;
private void btSave_Click(object sender, EventArgs e)
{
//保存新添加的行
try
{
int ColumnNum = dataGridView1.ColumnCount;
int RowNum = dataGridView1.Rows.Count;
dataGridView1.AutoGenerateColumns = true;
string ColumnValue = null; ;//存放新加入的一行信息序列
string ColumnName = null; ;//加入行的列名
for (int i = 0; i < ColumnNum; i++)
{
ColumnValue += "'"+dataGridView1.Rows[RowNum - 2].Cells[i].Value+ "',";
ColumnName += dataGridView1.Columns[i].Name + ","; }
ColumnValue = ColumnValue.Substring(0, ColumnValue.Length - 1);
ColumnName = ColumnName.Substring(0, ColumnName.Length - 1);
string strSQL = "insert " + cbxTablesList.Text + " " + "(" + ColumnName + ")" + " values" + "(" + ColumnValue + ")";
CExecuteSQL cex = new CExecuteSQL();
cex.ExecuteSQL(strSQL);
MessageBox.Show("恭喜你,保存成功!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "警告", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}和我的想法一样,不过插入的时候我是按照string类型插入的,竟然没报错,怎么根据数据库表的字段类型插入啊?
另外,如果你的n都未知,那么表的列数是否已知?
n=datagridview.ColumnCount;