1、sqlserver数据简单操作
sqlserver数据库连接字符串格式:server=服务器地址; pooling=true;max pool size=100; min pool size=5;packet size=4096;database=数据库名称;uid=用户名;pwd=密码;connection timeout=150;
///
<summary>
///
根据查询语句从数据库中获取,指定数据
///
</summary>
///
<param
name="sql">查询sql</param>
/// <param
name="connStr">连接数据库字符串</param>
///
<returns>结果数据</returns>
public DataTable GetDataTable(string sql, string
connStr)
{
SqlConnection conn = new
SqlConnection(connStr);
SqlCommand com =
null;
SqlDataAdapter adapter =
null;
try
{
conn.Open();
com =
conn.CreateCommand();
com.CommandText =
sql;
adapter = new
SqlDataAdapter(com);
DataTable dataTab = new
DataTable();
adapter.Fill(dataTab);
return
dataTab;
}
catch (System.Exception
ex)
{
LogManage.CreateObject().ExportLog(LogType.Exception,
m_UserName,
string.Format("执行sql语句:{0} 失败![异常信息:{1}]", sql, ex.Message),
"SqlServerDataProvider.GetDataTable");
return
null;
}
finally
{
if (com !=
null)
{
com.Dispose();
}
if (adapter !=
null)
{
adapter.Dispose();
}
conn.Close();
}
}
/// <summary>
/// 将数据插入指定数据表中
///
</summary>
///
<param
name="dataTab">数据</param>
/// <param
name="tableName">表名</param>
/// <param
name="connStr">连接字符串</param>
///
<returns>执行成功返回true</returns>
public bool InsertDataIntoDB(DataTable dataTab, string tableName,
string connStr)
{
SqlConnection conn = new
SqlConnection(connStr);
SqlCommand com =
null;
SqlTransaction tran =
null;
try
{
conn.Open();
tran =
conn.BeginTransaction();
com =
conn.CreateCommand();
com.Transaction =
tran;
SqlBulkCopy bulk = new SqlBulkCopy(conn,
SqlBulkCopyOptions.TableLock,
tran);
bulk.DestinationTableName =
tableName;
bulk.BatchSize =
1000;
bulk.WriteToServer(dataTab);
tran.Commit();
return
true;
}
catch (System.Exception
ex)
{
if (tran !=
null)
{
tran.Rollback();
}
LogManage.CreateObject().ExportLog(LogType.Exception,
m_UserName,
string.Format("向表{0}中插入数据失败![异常信息:{1}]", tableName, ex.Message),
"SqlServerDataProvider.InsertDataIntoDB");
return
false;
}
finally
{
if (com !=
null)
{
com.Dispose();
}
conn.Close();
}
}