62,046
社区成员
发帖
与我相关
我的任务
分享
public void Exsql(string sql,string tabel,string code , string name)//把数据写入数据库
{
open();
SqlCommand comm = new SqlCommand(sql, mydata.con);//sql为插入储存过程
comm.CommandType = CommandType.StoredProcedure;
comm.Parameters.Add(new SqlParameter("@tabel", SqlDbType.VarChar, 50)).Value = tabel;
comm.Parameters.Add(new SqlParameter("@a", SqlDbType.VarChar, 50)).Value = code;
comm.Parameters.Add(new SqlParameter("@b", SqlDbType.VarChar, 50)).Value = name;
mydata.ExecSql(comm);
close();
}
public void insetdata(string str)
{
string[][] tabel = rechar.tabel_Array(str, ' ', '^');//取得数据有100000条
data _data = new data();
for (int i = 0; i < tabel.Length; i++)
{
_data.Exsql("insert", "sz", tabel[i][0], tabel[i][1]);//调用上面的写入数据库。这样明显每次打开关闭数据库。肯定不行,请大家给我支支招
}
}
public SqlDataReader ExecSql(SqlCommand com)
{
SqlDataReader Dr = com.ExecuteReader(CommandBehavior.CloseConnection);
return Dr;
}
public void Exsql(string sql,string tabel,string code , string name)//把数据写入数据库
{
SqlCommand comm = new SqlCommand(sql, mydata.con);//sql为插入储存过程
comm.CommandType = CommandType.StoredProcedure;
comm.Parameters.Add(new SqlParameter("@tabel", SqlDbType.VarChar, 50)).Value = tabel;
comm.Parameters.Add(new SqlParameter("@a", SqlDbType.VarChar, 50)).Value = code;
comm.Parameters.Add(new SqlParameter("@b", SqlDbType.VarChar, 50)).Value = name;
mydata.ExecSql(comm);
}
public void insetdata(string str)
{
string[][] tabel = rechar.tabel_Array(str, ' ', '^');//取得数据有100000条
data _data = new data();
Open()
for (int i = 0; i < tabel.Length; i++)
{
_data.Exsql("insert", "sz", tabel[i][0], tabel[i][1])//这样不行,因为comm只能打开一次执行一次
string path = Server.MapPath("a.csv").ToLower();
string path2 = path.Replace(".csv",".xls");
SELECT * into newtable
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="a.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
/// <summary>
/// 执行多条SQL语句,实现数据库事务。
/// </summary>
/// <param name="SQLStringList">SQL语句的哈希表(key为sql语句,value是该语句的SqlParameter[])</param>
//public static void ExecuteSqlTran(Hashtable SQLStringList)
//{
// using (SqlConnection conn = new SqlConnection(connectionString))
// {
// conn.Open();
// using (SqlTransaction trans = conn.BeginTransaction())
// {
// SqlCommand cmd = new SqlCommand();
// try
// {
// //循环
// foreach (DictionaryEntry myDE in SQLStringList)
// {
// string cmdText = myDE.Key.ToString();
// SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
// PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
// int val = cmd.ExecuteNonQuery();
// cmd.Parameters.Clear();
//
}
// trans.Commit();
// }
// catch
// {
// trans.Rollback();
// throw;
// }
// }
// }
//}
CREATE PROCEDURE [dbo].[insert2]
AS
BEGIN
INSERT gatetable
select [HAIKWAN_TYPE],[HAIKWAN_NAME]
from OpenDataSource('MICROSOFT.JET.OLEDB.4.0','Text;HDR=Yes;DATABASE=E:\海关数据\')...[gate#csv]
END
INSERT gatetable
select [HAIKWAN_TYPE],[HAIKWAN_NAME]
from OpenDataSource('MICROSOFT.JET.OLEDB.4.0','Text;HDR=Yes;DATABASE=E:\海关数据\')...[gate#csv]
--sp_configure 'show advanced options', 1;
--GO
--RECONFIGURE;
--GO
--sp_configure 'Ad Hoc Distributed Queries', 1;
--GO
--RECONFIGURE;
--GO
INSERT gatetable
select [HAIKWAN_TYPE],[HAIKWAN_NAME]
from
OpenDataSource('MICROSOFT.JET.OLEDB.4.0','Text;HDR=Yes;DATABASE=E:\海关数据\')...[gate#csv]