请教各位:把Dataset中的数据批量插入到数据库中 Dataset是从sybase中取出的,我想批量插入到Oracle中,大家有什么好方法 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 写个转换吧。我写的到sql的转换你看看: foreach(DataRow row in this.dataTable1.Rows) { this.ultraProgressBar1.Value++; string s1=""; string s2=""; foreach(DataColumn col in this.dataTable1.Columns) { s1=s1+","+col.ColumnName; s2=s2+",'"+row[col.ColumnName].ToString()+"'"; } try { s1=s1.Substring(1); s2=s2.Substring(1); new cmdExecute("insert into TABLE_人员 ("+s1+") values ("+s2+")"); } catch { this.listBox1.Items.Add("导入 "+row["编号"].ToString()+" 时发生错误!忽略此项。"); } }虽然程序写的不好,但是比较实用。 private static readonly string InsertSql= "Insert {0}({1}) values({2})";public static void InsertTableData(DataTable dtb){ string TableName = dtb.TableName; string PKName= TableName + "ID"; StringBuilder insertValues = new StringBuilder(); StringBuilder cloumn_list = new StringBuilder(); for (int k=0; k<dtb.Columns.Count; ++k) { if (dtb.Columns[k].ColumnName.ToLower() == "version") { continue; } cloumn_list.Append(dtb.Columns[k].ColumnName); insertValues.Append("@"+dtb.Columns[k].ColumnName); cloumn_list.Append(","); insertValues.Append(","); } if (cloumn_list.Length > 0) { cloumn_list.Remove(cloumn_list.Length-1, 1); insertValues.Remove(insertValues.Length-1, 1); } for (int i=0; i<dtb.Rows.Count; ++i) { System.Data.IDataParameter[] parameters = GetDataParameters(dtb, i); string sql = string.Format(InsertSql, TableName, cloumn_list.ToString(), insertValues.ToString()); ExecuteNonQuery(sql, parameters); }} 大概思路就是传一个datatable进来,然后循环插入 private static System.Data.DbType GetDbType(Type type){ DbType result = DbType.String; if( type.Equals(typeof(int)) || type.IsEnum) result = DbType.Int32; else if( type.Equals(typeof(long))) result = DbType.Int32; else if( type.Equals(typeof(double)) || type.Equals( typeof(Double))) result = DbType.Decimal; else if( type.Equals(typeof(DateTime))) result = DbType.DateTime; else if( type.Equals(typeof(bool))) result = DbType.Boolean; else if( type.Equals(typeof(string) ) ) result = DbType.String; else if( type.Equals(typeof(decimal))) result = DbType.Decimal; else if( type.Equals(typeof(byte[]))) result = DbType.Binary; else if( type.Equals(typeof(Guid))) result = DbType.Guid; return result; }public static void UpdateTable(DataTable dt,string TableName,string KeyName){ foreach(DataRow dr in dt.Rows) { updateRow(dr,TableName,KeyName); }}public static void InsertTable(DataTable dt){string TableName="["+dt.TableName+"]"; string KeyName=dt.TableName+"ID"; foreach(DataRow dr in dt.Rows) { insertRow(dr,TableName,KeyName); }}private static void updateRow(DataRow dr,string TableName,string KeyName){ if (dr[KeyName]==DBNull.Value ) { throw new Exception(KeyName +"的值不能为空"); return; } if (dr.RowState ==DataRowState.Deleted) { deleteRow(dr,TableName,KeyName); } else if (dr.RowState ==DataRowState.Modified ) { midifyRow(dr,TableName,KeyName); } else if (dr.RowState ==DataRowState.Added ) { insertRow(dr,TableName,KeyName); } else if (dr.RowState ==DataRowState.Unchanged ) { midifyRow(dr,TableName,KeyName); } }private static void deleteRow(DataRow dr,string TableName,string KeyName){ string sql="Delete {0} where {1} =@{1}"; DataTable dtb=dr.Table ; sql=string.Format(sql,TableName,KeyName); SqlCommand sqlcom=new SqlCommand(sql); System.Data.IDataParameter iparam=new SqlParameter(); iparam.ParameterName = "@"+ KeyName; iparam.DbType = GetDbType(dtb.Columns[KeyName].DataType); iparam.Value = dr[KeyName]; sqlcom.Parameters .Add(iparam); ExecCommand(sqlcom);}private static void midifyRow(DataRow dr,string TableName,string KeyName){ string UpdateSql = "Update {0} set {1} {2}"; string setSql="{0}= @{0}"; string wherSql=" Where {0}=@{0}"; StringBuilder setSb= new StringBuilder(); SqlCommand sqlcom=new SqlCommand(); DataTable dtb=dr.Table ; for (int k=0; k<dr.Table .Columns.Count; ++k) { System.Data.IDataParameter iparam=new SqlParameter(); iparam.ParameterName= "@"+ dtb.Columns[k].ColumnName; iparam.DbType= GetDbType(dtb.Columns[k].DataType); iparam.Value= dr[k]; sqlcom.Parameters .Add(iparam); if (dtb.Columns[k].ColumnName==KeyName) { wherSql=string.Format(wherSql,KeyName); } else { setSb.Append(string.Format(setSql,dtb.Columns[k].ColumnName)); setSb.Append(","); } } string setStr=setSb.ToString();setStr=setStr.Substring(0,setStr.Length -1); //trim , string sql = string.Format(UpdateSql, TableName, setStr,wherSql);sqlcom.CommandText =sql; try{ ExecCommand(sqlcom);}catch(Exception ex){throw ex; }}private static void insertRow(DataRow dr,string TableName,string KeyName){ string InsertSql = "Insert into {0}({1}) values({2})"; SqlCommand sqlcom=new SqlCommand(); DataTable dtb=dr.Table ; StringBuilder insertValues = new StringBuilder(); StringBuilder cloumn_list = new StringBuilder(); for (int k=0; k<dr.Table .Columns.Count; ++k) { //just for 自动增长, if (dtb.Columns[k].ColumnName==KeyName) continue; System.Data.IDataParameter iparam=new SqlParameter(); iparam.ParameterName= "@"+ dtb.Columns[k].ColumnName; iparam.DbType= GetDbType(dtb.Columns[k].DataType); iparam.Value= dr[k]; sqlcom.Parameters .Add(iparam); cloumn_list.Append(dtb.Columns[k].ColumnName); insertValues.Append("@"+dtb.Columns[k].ColumnName); cloumn_list.Append(","); insertValues.Append(","); } string cols=cloumn_list.ToString(); cols=cols.Substring(0,cols.Length -1); string values=insertValues.ToString(); values=values.Substring(0,values.Length -1); string sql = string.Format(InsertSql, TableName,cols ,values); sqlcom.CommandText =sql; try { ExecCommand(sqlcom); } catch(Exception ex) { throw ex; }}public static int ExecCommand(string sql){ if (sql.EndsWith(",")) sql=sql.Substring(0,sql.Length-1); SqlCommand sqlcom=new SqlCommand(sql); return ExecCommand(sqlcom); } sqlserver 求时间差问题 左右两个按钮 点击按钮月份增减 急!c#调用oracle函数返回结果集,在线等... 这也太鬼异了吧? 怎么样记录用户对程序的所有的操作日志? ·一段简单的C#代码不解其意,请各位长者们点解? 开发浩方电竞平台这样的程序的疑问! qq软件上点击qq邮箱后,自动登录qq邮箱是如何实现的 .net中 需要传值的事件怎么调用? 求助:用COM对象编程时,解决版本兼容的问题 一个简单的Winform问题 ~~~~~~~~~~~~~`~~C#中如何取得本机IP,100分相送!~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
{
this.ultraProgressBar1.Value++; string s1="";
string s2=""; foreach(DataColumn col in this.dataTable1.Columns)
{
s1=s1+","+col.ColumnName;
s2=s2+",'"+row[col.ColumnName].ToString()+"'";
} try
{
s1=s1.Substring(1);
s2=s2.Substring(1); new cmdExecute("insert into TABLE_人员 ("+s1+") values ("+s2+")");
}
catch
{
this.listBox1.Items.Add("导入 "+row["编号"].ToString()+" 时发生错误!忽略此项。");
}
}
虽然程序写的不好,但是比较实用。
public static void InsertTableData(DataTable dtb)
{
string TableName = dtb.TableName;
string PKName= TableName + "ID";
StringBuilder insertValues = new StringBuilder();
StringBuilder cloumn_list = new StringBuilder();
for (int k=0; k<dtb.Columns.Count; ++k)
{
if (dtb.Columns[k].ColumnName.ToLower() == "version")
{
continue;
}
cloumn_list.Append(dtb.Columns[k].ColumnName);
insertValues.Append("@"+dtb.Columns[k].ColumnName);
cloumn_list.Append(",");
insertValues.Append(",");
}
if (cloumn_list.Length > 0)
{
cloumn_list.Remove(cloumn_list.Length-1, 1);
insertValues.Remove(insertValues.Length-1, 1);
}
for (int i=0; i<dtb.Rows.Count; ++i)
{
System.Data.IDataParameter[] parameters = GetDataParameters(dtb, i);
string sql = string.Format(InsertSql, TableName, cloumn_list.ToString(), insertValues.ToString());
ExecuteNonQuery(sql, parameters);
}
}
{
DbType result = DbType.String;
if( type.Equals(typeof(int)) || type.IsEnum)
result = DbType.Int32;
else if( type.Equals(typeof(long)))
result = DbType.Int32;
else if( type.Equals(typeof(double)) || type.Equals( typeof(Double)))
result = DbType.Decimal;
else if( type.Equals(typeof(DateTime)))
result = DbType.DateTime;
else if( type.Equals(typeof(bool)))
result = DbType.Boolean;
else if( type.Equals(typeof(string) ) )
result = DbType.String;
else if( type.Equals(typeof(decimal)))
result = DbType.Decimal;
else if( type.Equals(typeof(byte[])))
result = DbType.Binary;
else if( type.Equals(typeof(Guid)))
result = DbType.Guid;
return result;
}
public static void UpdateTable(DataTable dt,string TableName,string KeyName)
{
foreach(DataRow dr in dt.Rows)
{
updateRow(dr,TableName,KeyName);
}
}
public static void InsertTable(DataTable dt)
{
string TableName="["+dt.TableName+"]";
string KeyName=dt.TableName+"ID";
foreach(DataRow dr in dt.Rows)
{
insertRow(dr,TableName,KeyName);
}
}
private static void updateRow(DataRow dr,string TableName,string KeyName)
{
if (dr[KeyName]==DBNull.Value )
{
throw new Exception(KeyName +"的值不能为空");
return;
}
if (dr.RowState ==DataRowState.Deleted)
{
deleteRow(dr,TableName,KeyName);
}
else if (dr.RowState ==DataRowState.Modified )
{
midifyRow(dr,TableName,KeyName);
}
else if (dr.RowState ==DataRowState.Added )
{
insertRow(dr,TableName,KeyName);
}
else if (dr.RowState ==DataRowState.Unchanged )
{
midifyRow(dr,TableName,KeyName);
}
}
private static void deleteRow(DataRow dr,string TableName,string KeyName)
{
string sql="Delete {0} where {1} =@{1}";
DataTable dtb=dr.Table ;
sql=string.Format(sql,TableName,KeyName);
SqlCommand sqlcom=new SqlCommand(sql);
System.Data.IDataParameter iparam=new SqlParameter();
iparam.ParameterName = "@"+ KeyName;
iparam.DbType = GetDbType(dtb.Columns[KeyName].DataType);
iparam.Value = dr[KeyName];
sqlcom.Parameters .Add(iparam);
ExecCommand(sqlcom);
}
private static void midifyRow(DataRow dr,string TableName,string KeyName)
{
string UpdateSql = "Update {0} set {1} {2}";
string setSql="{0}= @{0}";
string wherSql=" Where {0}=@{0}";
StringBuilder setSb= new StringBuilder();
SqlCommand sqlcom=new SqlCommand();
DataTable dtb=dr.Table ;
for (int k=0; k<dr.Table .Columns.Count; ++k)
{
System.Data.IDataParameter iparam=new SqlParameter();
iparam.ParameterName= "@"+ dtb.Columns[k].ColumnName;
iparam.DbType= GetDbType(dtb.Columns[k].DataType);
iparam.Value= dr[k];
sqlcom.Parameters .Add(iparam);
if (dtb.Columns[k].ColumnName==KeyName)
{
wherSql=string.Format(wherSql,KeyName);
}
else
{
setSb.Append(string.Format(setSql,dtb.Columns[k].ColumnName)); setSb.Append(",");
}
}
string setStr=setSb.ToString();
setStr=setStr.Substring(0,setStr.Length -1); //trim ,
string sql = string.Format(UpdateSql, TableName, setStr,wherSql);
sqlcom.CommandText =sql;
try
{
ExecCommand(sqlcom);
}
catch(Exception ex)
{
throw ex;
}
}
private static void insertRow(DataRow dr,string TableName,string KeyName)
{
string InsertSql = "Insert into {0}({1}) values({2})";
SqlCommand sqlcom=new SqlCommand();
DataTable dtb=dr.Table ;
StringBuilder insertValues = new StringBuilder();
StringBuilder cloumn_list = new StringBuilder();
for (int k=0; k<dr.Table .Columns.Count; ++k)
{
//just for 自动增长,
if (dtb.Columns[k].ColumnName==KeyName) continue;
System.Data.IDataParameter iparam=new SqlParameter();
iparam.ParameterName= "@"+ dtb.Columns[k].ColumnName;
iparam.DbType= GetDbType(dtb.Columns[k].DataType);
iparam.Value= dr[k];
sqlcom.Parameters .Add(iparam);
cloumn_list.Append(dtb.Columns[k].ColumnName);
insertValues.Append("@"+dtb.Columns[k].ColumnName);
cloumn_list.Append(",");
insertValues.Append(",");
}
string cols=cloumn_list.ToString();
cols=cols.Substring(0,cols.Length -1);
string values=insertValues.ToString();
values=values.Substring(0,values.Length -1);
string sql = string.Format(InsertSql, TableName,cols ,values);
sqlcom.CommandText =sql;
try
{
ExecCommand(sqlcom);
}
catch(Exception ex)
{
throw ex;
}
}
public static int ExecCommand(string sql)
{
if (sql.EndsWith(",")) sql=sql.Substring(0,sql.Length-1);
SqlCommand sqlcom=new SqlCommand(sql);
return ExecCommand(sqlcom);
}