你这个数据已经读出来了,从新建一个到数据库的连接,使用
INSERT INTO DBNAME (COL1,COL2...) VALUES(VALUE1, VALUE2,...) 去插入就行了阿或者直接用dataadapter也行啊
INSERT INTO DBNAME (COL1,COL2...) VALUES(VALUE1, VALUE2,...) 去插入就行了阿或者直接用dataadapter也行啊
public void subImportCsv_Dataset(string filePath, string fileName)
{
string strConn = @"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=";
strConn += filePath;//这个地方只需要目录就可以了
strConn += ";Extensions=asc,csv,tab,txt;";
OdbcConnection objConn = new OdbcConnection(strConn); string strSQL = "select * from " + fileName;//文件名,不要带目录
OdbcDataAdapter da = new OdbcDataAdapter(strSQL, objConn);
DataSet ds = new DataSet();
da.Fill(ds);
//开始导入数据库
string sql = "insert into AHU1_数据记录0 (VarName,TimeString,VarValue,Validity,Time_ms) values (@VarName,@TimeString,@VarValue,@Validity,@Time_ms)";
SqlConnection con = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Database1.mdf;Integrated Security=True;User Instance=True");
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
SqlParameter p = new SqlParameter("@VarName", SqlDbType.NVarChar);
cmd.Parameters.Add(p);
p = new SqlParameter("@TimeString", SqlDbType.NVarChar);
cmd.Parameters.Add(p);
p = new SqlParameter("@VarValue", SqlDbType.NVarChar);
cmd.Parameters.Add(p);
p = new SqlParameter("@Validity", SqlDbType.NVarChar);
cmd.Parameters.Add(p);
p = new SqlParameter("@Time_ms", SqlDbType.NVarChar);
cmd.Parameters.Add(p);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
cmd.Parameters["@VarName"].Value = ds.Tables[0].Rows[i]["VarName"].ToString();
cmd.Parameters["@TimeString"].Value = ds.Tables[0].Rows[i]["TimeString"].ToString();
cmd.Parameters["@VarValue"].Value = ds.Tables[0].Rows[i]["VarValue"].ToString();
cmd.Parameters["@Validity"].Value = ds.Tables[0].Rows[i]["Validity"].ToString();
cmd.Parameters["@Time_ms"].Value = ds.Tables[0].Rows[i]["Time_ms"].ToString();
cmd.ExecuteNonQuery();
} }正常数据操作不行啊,我这样试过。
http://topic.csdn.net/u/20110110/03/bf632b7c-b12c-4484-a41e-a1c4fa3ce759.html
也可以读出文件内容,结合成语句插入数据库
下面这个是我前几天些的一个函数:
#region csv文件操作
public static DataSet dataset_csv(string sql,string fileurl)
{
OleDbConnection oleconn = new OleDbConnection();
OleDbCommand olecmd = new OleDbCommand();
OleDbDataAdapter oleadp;
DataSet csvdataset;
oleconn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+fileurl+";Extended Properties='Text;FMT=Delimited;HDR=YES;'";
using (oleadp = new OleDbDataAdapter(sql, oleconn))
{
using (csvdataset = new DataSet("csv"))
{
oleadp.Fill(csvdataset, "csvtable");
return csvdataset;
}
}
olecmd.Dispose();
oleconn.Close();
}
#endregion
/// 将DataTable导入SqlServer数据库
/// </summary>
/// <param name="dt">DataTable数据</param>
/// <param name="strIP">数据库ip</param>
/// <param name="strDatabase">数据库名</param>
/// <param name="strUserId">用户名</param>
/// <param name="strPwd">密码</param>
/// <param name="strTableName">要导入的表名</param>
/// <returns>错误信息(无错误返回空字符串)</returns>
public string DataTableToSqlServer(DataTable dt,string strIP,string strDatabase,string strUserId,string strPwd,string strTableName)
{
string strError = "";
SqlBulkCopy SqlBulk = new SqlBulkCopy("Data Source=" + strIP + ";Initial Catalog=" + strDatabase + ";User Id=" + strUserId + ";Password=" + strPwd + ";");
SqlBulk.DestinationTableName = strTableName;
try
{
SqlBulk.WriteToServer(dt);
}
catch(Exception ex)
{
strError = ex.Message + ":" + ex.StackTrace;
}
finally
{
SqlBulk.Close();
}
return strError;
}DataTable中列的数据类型要和sqlserver中的列类型一一对应。
private static bool csvToSql(string fileName)
{
string StrConn = "server=.;database='" + DataBaseName + "';uid='" + userName + "';pwd='" + Password + "'"; SqlConnection connection = new SqlConnection(StrConn); try
{
connection.Open(); string cmdtxt = @"bulk insert " + TableName + " from '" + FilePath + fileName + "' with(FIRE_TRIGGERS,FIELDTERMINATOR=',')"; SqlCommand cmd = new SqlCommand(cmdtxt, connection); cmd.ExecuteNonQuery(); return true;
}
catch (System.Exception ex)
{
// log.WriteLine("文件:{0} error:{1}", fileName, ex.Message);
// log.Flush();
return false;
} }
{
subImportCsv_Dataset("C:\\data\\","AHU1_数据记录0.csv");
csvToSql("C:\\data\\","AHU1_数据记录0.csv");
}
public void subImportCsv_Dataset(string filePath, string fileName)
{
string strConn = @"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=";
strConn += filePath;//这个地方只需要目录就可以了
strConn += ";Extensions=asc,csv,tab,txt;";
OdbcConnection objConn = new OdbcConnection(strConn); string strSQL = "select * from " + fileName;//文件名,不要带目录
OdbcDataAdapter da = new OdbcDataAdapter(strSQL, objConn);
DataSet ds = new DataSet();
da.Fill(ds); }
private static bool csvToSql(string filePath, string fileName)
{
string StrConn = "Data Source=.;AttachDbFilename=|DataDirectory|\\App_Data\\Database1.mdf;Integrated Security=True;User Instance=True";
SqlConnection connection = new SqlConnection(StrConn); try
{
connection.Open(); string cmdtxt = @"bulk insert Table from '" + filePath + fileName + "' with(FIRE_TRIGGERS,FIELDTERMINATOR=',')"; SqlCommand cmd = new SqlCommand(cmdtxt, connection); cmd.ExecuteNonQuery(); return true;
}
catch (System.Exception ex)
{
// log.WriteLine("文件:{0} error:{1}", fileName, ex.Message);
// log.Flush();
return false;
} }
cmdtxt 是否正确,
bulk insert Table from 你的这个Table应该是要插入的表明
你在数据库执行这个,如果可以,就是你程序那里写错了,如果有问题,可能就是你cvs文件包含特殊字符
{
string strConn = @"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=";
strConn += filePath;//这个地方只需要目录就可以了
strConn += ";Extensions=asc,csv,tab,txt;";
OdbcConnection objConn = new OdbcConnection(strConn); try
{
string strSQL = "select * from " + fileName;//文件名,不要带目录
OdbcDataAdapter da = new OdbcDataAdapter(strSQL, objConn);
DataSet ds = new DataSet();
da.Fill(ds);
return ds;
}
catch (Exception ex)
{
throw ex;
return null;
}
}
public int UpdateResult(DataSet ds)
{
int icount=0;
SqlConnection con = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Database1.mdf;Integrated Security=True;User Instance=True");
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
SqlParameter p = new SqlParameter("@VarName", SqlDbType.NVarChar);
cmd.Parameters.Add(p);
p = new SqlParameter("@TimeString", SqlDbType.NVarChar);
cmd.Parameters.Add(p);
p = new SqlParameter("@VarValue", SqlDbType.NVarChar);
cmd.Parameters.Add(p);
p = new SqlParameter("@Validity", SqlDbType.NVarChar);
cmd.Parameters.Add(p);
p = new SqlParameter("@Time_ms", SqlDbType.NVarChar);
cmd.Parameters.Add(p);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
cmd.Parameters["@VarName"].Value = ds.Tables[0].Rows[i]["VarName"].ToString();
cmd.Parameters["@TimeString"].Value = ds.Tables[0].Rows[i]["TimeString"].ToString();
cmd.Parameters["@VarValue"].Value = ds.Tables[0].Rows[i]["VarValue"].ToString();
cmd.Parameters["@Validity"].Value = ds.Tables[0].Rows[i]["Validity"].ToString();
cmd.Parameters["@Time_ms"].Value = ds.Tables[0].Rows[i]["Time_ms"].ToString();
conn.Open();
if(cmd.ExecuteNonQuery()>0)
icount++; cmd.Close();
}打开关闭应该放在循环内做
{
int icount = 0, itemp = 0;
SqlConnection con = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Database1.mdf;Integrated Security=True;User Instance=True");
SqlCommand cmd = new SqlCommand(sql, con);
SqlParameter[] p = new SqlParameter[]
{
new SqlParameter("@VarName", SqlDbType.NVarChar)
, new SqlParameter("@TimeString", SqlDbType.NVarChar)
, new SqlParameter("@VarValue", SqlDbType.NVarChar)
, new SqlParameter("@Validity", SqlDbType.NVarChar)
, new SqlParameter("@Time_ms", SqlDbType.NVarChar)};
foreach (SqlParameter par in p)
cmd.Parameters.Add(p);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
cmd.Parameters["@VarName"].Value = ds.Tables[0].Rows[i]["VarName"].ToString();
cmd.Parameters["@TimeString"].Value = ds.Tables[0].Rows[i]["TimeString"].ToString();
cmd.Parameters["@VarValue"].Value = ds.Tables[0].Rows[i]["VarValue"].ToString();
cmd.Parameters["@Validity"].Value = ds.Tables[0].Rows[i]["Validity"].ToString();
cmd.Parameters["@Time_ms"].Value = ds.Tables[0].Rows[i]["Time_ms"].ToString();
con.Open();
itemp = cmd.ExecuteNonQuery();
if (itemp > 0)
{
icount += itemp;
}
else
{
icount -= itemp;
}
con.Close();
}
return icount;
}
这个在数据库可以执行,我数据库是建在项目文件夹中的APP_Data文件夹中的Database1.mdf连接字符串我也看不出什么问题啊。
{
SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["WindowsFormsApplication3.Properties.Settings.Database1ConnectionString2"].ConnectionString); try
{
connection.Open(); string cmdtxt = @"bulk insert [Table] from "+ filePath + fileName +" with(fieldterminator=',',rowterminator='\n')"; SqlCommand cmd = new SqlCommand(cmdtxt, connection); cmd.ExecuteNonQuery(); return true;
}
catch (System.Exception ex)
{
// log.WriteLine("文件:{0} error:{1}", fileName, ex.Message);
// log.Flush();
return false;
} }
<connectionStrings>
<add name="WindowsFormsApplication3.Properties.Settings.Database1ConnectionString2"
connectionString="Data Source=KYO\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>我实在是搞不懂了,这连接字符串是系统自动生成的,不会有问题吧,程序也看了N遍了,断点也试了一直执行得很顺利,就是数据库里不添加数据。
我孤陋寡闻了