Dataset是从sybase中取出的,我想批量插入到Oracle中,大家有什么好方法
解决方案 »
- 求此代码的完美注释。
- c#中excel数据导入到access2007的代码???
- txt文本文件抽取字符串问题
- COM 组件使用
- .net如何查询数据库的数据按格式导出到word文档中??
- 如何将图片存储到数据库中?
- OracleConnection 连接池 观察oracle 确实增加的连接...但是执行还是在同一个连接执行的...
- C#控制word,怎么才能插入一个页眉,页眉中包括图片与文字
- 关于序列化的一个问题,一直没有想明白
- 求教——如何使用Udpclient发送和接收广播信息
- 一个简单的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);
}