using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
namespace MyInterface
{
public class Class_DB_SQL
{
//数据库连接字符
// public string str_con = @"Data Source=(local);Integrated Security=SSPI;Initial Catalog=Book;";
// public string str_con = @"server = Tripodcn-Test;database = R2_SQL2B;uid = sa;pwd = 2013_test;";
public string Server { get; set; } = "tripodcn-test";
public string Database { get; set; } = "r2_sql2b";
public string Uid { get; set; } = "sa";
public string Pwd { get; set; } = "2013_test";
public string Str_con { get; set; }
public void Str_conInit()
{
Str_con = @"server = " + Server + ";database =" + Database + ";uid =" + Uid + ";pwd =" + Pwd + ";";
}//初始化連線
SqlConnection conn;
#region public object Get_word(string sql) 根据sql语句返回第一行第一列的值
/// <summary>
/// 根据sql语句返回第一行第一列的值
/// </summary>
/// <param name="sql">sql语句</param>
/// <returns>查询结果</returns>
public object Get_word(string sql)
{
try
{
conn = new SqlConnection(Str_con);
conn.Open();
SqlCommand comm = new SqlCommand(sql, conn);
return comm.ExecuteScalar();
}
catch
{
//conn.Close();
throw;
}
finally
{
conn.Close();
}
}
#endregion
#region public DataTable Get_table(string sql) 根据sql语句返回查询表
/// <summary>
/// 根据sql语句返回查询表
/// </summary>
/// <param name="sql">sql语句</param>
/// <returns>结果表</returns>
public DataTable Get_table(string sql)
{
try
{
conn = new SqlConnection(Str_con);
conn.Open();
SqlCommand comm = new SqlCommand(sql, conn);
SqlDataAdapter da = new SqlDataAdapter(comm);
DataSet ds = new DataSet();
da.Fill(ds);
return ds.Tables[0];
}
catch
{
//conn.Close();
throw;
}
finally
{
conn.Close();
}
}
#endregion
#region public int Execute_sql(string sql) 执行sql语句,返回受影响的行数
/// <summary>
/// 执行sql语句,返回受影响的行数
/// </summary>
/// <param name="sql">执行的sql语句</param>
/// <returns>受影响的行数</returns>
public int Execute_sql(string sql)
{
try
{
conn = new SqlConnection(Str_con);
conn.Open();
SqlCommand comm = new SqlCommand(sql, conn);
return comm.ExecuteNonQuery();
}
catch
{
throw;
}
finally
{
conn.Close();
}
}
#endregion
#region 使用SqlBulkCopy将DataTable中的数据批量插入数据库中
/// <summary>
/// 注意:DataTable中的列需要与数据库表中的列完全一致。
/// 已自测可用。
/// </summary>
/// <param name="conStr">数据库连接串</param>
/// <param name="strTableName">数据库中对应的表名</param>
/// <param name="dtData">数据集</param>
public void SqlBulkCopyInsert( string strTableName, DataTable dtData)
// public void SqlBulkCopyInsert(string str_con, string strTableName, DataTable dtData)
{
try
{
using (SqlBulkCopy sqlRevdBulkCopy = new SqlBulkCopy(Str_con))//引用SqlBulkCopy
{
sqlRevdBulkCopy.DestinationTableName = strTableName;//数据库中对应的表名
sqlRevdBulkCopy.NotifyAfter = dtData.Rows.Count;//有几行数据
sqlRevdBulkCopy.WriteToServer(dtData);//数据导入数据库
sqlRevdBulkCopy.Close();//关闭连接
}
}
catch (Exception ex)
{
throw (ex);
}
}
#endregion
}
}
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
namespace MyInterface
{
public class Class_DB_SQL
{
//数据库连接字符
// public string str_con = @"Data Source=(local);Integrated Security=SSPI;Initial Catalog=Book;";
// public string str_con = @"server = Tripodcn-Test;database = R2_SQL2B;uid = sa;pwd = 2013_test;";
public string Server { get; set; } = "tripodcn-test";
public string Database { get; set; } = "r2_sql2b";
public string Uid { get; set; } = "sa";
public string Pwd { get; set; } = "2013_test";
public string Str_con { get; set; }
public void Str_conInit()
{
Str_con = @"server = " + Server + ";database =" + Database + ";uid =" + Uid + ";pwd =" + Pwd + ";";
}//初始化連線
SqlConnection conn;
#region public object Get_word(string sql) 根据sql语句返回第一行第一列的值
/// <summary>
/// 根据sql语句返回第一行第一列的值
/// </summary>
/// <param name="sql">sql语句</param>
/// <returns>查询结果</returns>
public object Get_word(string sql)
{
try
{
conn = new SqlConnection(Str_con);
conn.Open();
SqlCommand comm = new SqlCommand(sql, conn);
return comm.ExecuteScalar();
}
catch
{
//conn.Close();
throw;
}
finally
{
conn.Close();
}
}
#endregion
#region public DataTable Get_table(string sql) 根据sql语句返回查询表
/// <summary>
/// 根据sql语句返回查询表
/// </summary>
/// <param name="sql">sql语句</param>
/// <returns>结果表</returns>
public DataTable Get_table(string sql)
{
try
{
conn = new SqlConnection(Str_con);
conn.Open();
SqlCommand comm = new SqlCommand(sql, conn);
SqlDataAdapter da = new SqlDataAdapter(comm);
DataSet ds = new DataSet();
da.Fill(ds);
return ds.Tables[0];
}
catch
{
//conn.Close();
throw;
}
finally
{
conn.Close();
}
}
#endregion
#region public int Execute_sql(string sql) 执行sql语句,返回受影响的行数
/// <summary>
/// 执行sql语句,返回受影响的行数
/// </summary>
/// <param name="sql">执行的sql语句</param>
/// <returns>受影响的行数</returns>
public int Execute_sql(string sql)
{
try
{
conn = new SqlConnection(Str_con);
conn.Open();
SqlCommand comm = new SqlCommand(sql, conn);
return comm.ExecuteNonQuery();
}
catch
{
throw;
}
finally
{
conn.Close();
}
}
#endregion
#region 使用SqlBulkCopy将DataTable中的数据批量插入数据库中
/// <summary>
/// 注意:DataTable中的列需要与数据库表中的列完全一致。
/// 已自测可用。
/// </summary>
/// <param name="conStr">数据库连接串</param>
/// <param name="strTableName">数据库中对应的表名</param>
/// <param name="dtData">数据集</param>
public void SqlBulkCopyInsert( string strTableName, DataTable dtData)
// public void SqlBulkCopyInsert(string str_con, string strTableName, DataTable dtData)
{
try
{
using (SqlBulkCopy sqlRevdBulkCopy = new SqlBulkCopy(Str_con))//引用SqlBulkCopy
{
sqlRevdBulkCopy.DestinationTableName = strTableName;//数据库中对应的表名
sqlRevdBulkCopy.NotifyAfter = dtData.Rows.Count;//有几行数据
sqlRevdBulkCopy.WriteToServer(dtData);//数据导入数据库
sqlRevdBulkCopy.Close();//关闭连接
}
}
catch (Exception ex)
{
throw (ex);
}
}
#endregion
}
}