public static void BatchInsert(DataTable dataTable, string destinationTableName, int batchSize = 0)
{
string connectionStr = SqlConnectionPool.GetConnectionString(SqlConnectionPool.SqlConnectionType.Write);
using (SqlConnection connection = new SqlConnection(connectionStr))
{
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
using (SqlTransaction transaction = connection.BeginTransaction())
{
//SqlBulkCopy 要求 DataTable 的列必须和表列顺序一致，并且不能多也不能少，所以实体类的字段顺序要和数据库表字段顺序和类型一致...
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction))
{
bulkCopy.BatchSize = batchSize;
bulkCopy.DestinationTableName = destinationTableName;
try
{
bulkCopy.WriteToServer(dataTable);
transaction.Commit();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
transaction.Rollback();
}
}
}
}
}
public DatabaseCommands(SqlConnectionPool connectionPool, string board)
{
ConnectionPool = connectionPool;
Board = board;
CreateTables(board);
InsertQuery = new MySqlCommand(string.Format(BaseInsertQuery, board));
InsertQuery.Parameters.Add("@num", MySqlDbType.UInt32, -1, "num");
InsertQuery.Parameters.Add("@thread_num", MySqlDbType.UInt32, -1, "thread_num");
InsertQuery.Parameters.Add("@op", MySqlDbType.Byte, -1, "op");
InsertQuery.Parameters.Add("@timestamp", MySqlDbType.UInt32, -1, "timestamp");
InsertQuery.Parameters.Add("@timestamp_expired", MySqlDbType.UInt32, -1, "timestamp_expired");
InsertQuery.Parameters.Add("@preview_orig", MySqlDbType.VarChar, 20, "preview_orig");
InsertQuery.Parameters.Add("@preview_w", MySqlDbType.UInt16, -1, "preview_w");
InsertQuery.Parameters.Add("@preview_h", MySqlDbType.UInt16, -1, "preview_h");
InsertQuery.Parameters.Add("@media_filename", MySqlDbType.Text, -1, "media_filename");
InsertQuery.Parameters.Add("@media_w", MySqlDbType.UInt16, -1, "media_w");
InsertQuery.Parameters.Add("@media_h", MySqlDbType.UInt16, -1, "media_h");
InsertQuery.Parameters.Add("@media_size", MySqlDbType.UInt32, -1, "media_size");
InsertQuery.Parameters.Add("@media_hash", MySqlDbType.VarChar, 25, "media_hash");
InsertQuery.Parameters.Add("@media_orig", MySqlDbType.VarChar, 20, "media_orig");
InsertQuery.Parameters.Add("@spoiler", MySqlDbType.Byte, -1, "spoiler");
InsertQuery.Parameters.Add("@deleted", MySqlDbType.Byte, -1, "deleted");
InsertQuery.Parameters.Add("@capcode", MySqlDbType.VarChar, 1, "capcode");
InsertQuery.Parameters.Add("@email", MySqlDbType.VarChar, 100, "email");
InsertQuery.Parameters.Add("@name", MySqlDbType.VarChar, 100, "name");
InsertQuery.Parameters.Add("@trip", MySqlDbType.VarChar, 25, "trip");
InsertQuery.Parameters.Add("@title", MySqlDbType.VarChar, 100, "title");
InsertQuery.Parameters.Add("@comment", MySqlDbType.Text, -1, "comment");
//InsertQuery.Parameters.Add("@delpass", MySqlDbType.TinyText);
InsertQuery.Parameters.Add("@sticky", MySqlDbType.Byte, -1, "sticky");
InsertQuery.Parameters.Add("@locked", MySqlDbType.Byte, -1, "locked");
InsertQuery.Parameters.Add("@poster_hash", MySqlDbType.VarChar, 8, "poster_hash");
InsertQuery.Parameters.Add("@poster_country", MySqlDbType.VarChar, 2, "poster_country");
//InsertQuery.Parameters.Add("@exif", MySqlDbType.Text);
postDataTable = new DataTable();
foreach (MySqlParameter param in InsertQuery.Parameters)
{
postDataTable.Columns.Add(param.ParameterName.Substring(1));
}
UpdateQuery = new MySqlCommand($"UPDATE `{board}` SET comment = @comment, deleted = @deleted, media_filename = COALESCE(@media_filename, media_filename), sticky = (@sticky OR sticky), locked = (@locked or locked) WHERE num = @thread_no AND subnum = @subnum");
UpdateQuery.Parameters.Add("@comment", MySqlDbType.Text);
UpdateQuery.Parameters.Add("@deleted", MySqlDbType.Byte);
UpdateQuery.Parameters.Add("@media_filename", MySqlDbType.Text);
UpdateQuery.Parameters.Add("@sticky", MySqlDbType.Byte);
UpdateQuery.Parameters.Add("@locked", MySqlDbType.Byte);
UpdateQuery.Parameters.Add("@thread_no", MySqlDbType.UInt32);
UpdateQuery.Parameters.Add("@subnum", MySqlDbType.UInt32);
DeleteQuery = new MySqlCommand($"UPDATE `{board}` SET deleted = 1, timestamp_expired = @timestamp_expired WHERE num = @thread_no AND subnum = 0");
DeleteQuery.Parameters.Add("@timestamp_expired", MySqlDbType.UInt32);
DeleteQuery.Parameters.Add("@thread_no", MySqlDbType.UInt32);
SelectHashQuery = new MySqlCommand($"SELECT num, locked, sticky, comment, media_filename FROM `{board}` WHERE deleted = 0 AND (num = @thread_no OR thread_num = @thread_no)");
SelectHashQuery.Parameters.Add("@thread_no", MySqlDbType.UInt32);
}
#pragma warning disable CA2214 // Constructor calls virtual method to retrieve required database connection class
/// <summary>Initializes a new instance of the <see cref="SqlStorage" /> class.</summary>
/// <param name="connectionString">the connection details.</param>
/// <param name="flags">The connection flags.</param>
protected SqlStorage(ConnectionString connectionString, ConnectionFlags flags = ConnectionFlags.None)
: base(connectionString, flags)
{
Trace.TraceInformation("Initializing native interop assemblies.");
using (var dbConnection = GetDbConnectionType())
using (var cmd = dbConnection.CreateCommand())
{
DbConnectionType = dbConnection.GetType();
}
pool = new SqlConnectionPool(this);
WarnUnsafe();
}
public AsagiThreadConsumer(AsagiConfig config, string[] boards)
{
Config = config;
ConnectionPool = new SqlConnectionPool(config.ConnectionString, config.SqlConnectionPoolSize);
foreach (string board in boards)
{
ConnectionPool.ForEachConnection(async connection => GetPreparedStatements(board).PrepareConnection(connection)).Wait();
}
ThumbDownloadLocation = Path.Combine(Config.DownloadLocation, "thumb");
ImageDownloadLocation = Path.Combine(Config.DownloadLocation, "images");
Directory.CreateDirectory(ThumbDownloadLocation);
Directory.CreateDirectory(ImageDownloadLocation);
}
/*
* Este padrão permite que objetos sejam reutilizados / reaproveitados, sem a necessidade de criação para cada chamada / solicitação de aplicações clientes. É uma espécie de cache de objetos.
*/
public void ObjectPool()
{
var constr = @"Data Source=.\SQLEXPRESS; Initial Catalog=NORTHWND; Integrated Security=true; Pooling=false;";
var pool = new SqlConnectionPool(constr);
var con = pool.checkOut();
var SQL = "select * from Products";
var cmd = new SqlCommand(SQL, con);
var dr = cmd.ExecuteReader();
while (dr.Read())
{
Console.WriteLine(dr[1].ToString());
}
dr.Close();
pool.checkIn(con);
Console.ReadLine();
}
/// <summary>
/// 执行sql命名返回一个影响行数,针对于增删改操作
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="isProcedure">是否是过程</param>
/// <param name="pms">sql语句所需要的参数</param>
/// <returns></returns>
public static int ExecuteNonQuery(string sql, bool isProcedure, params SqlParameter[] pms)
{
string connectionStr = SqlConnectionPool.GetConnectionString(SqlConnectionPool.SqlConnectionType.Write);
using (SqlConnection conn = new SqlConnection(connectionStr))
{
SqlCommand cmd = new SqlCommand(sql, conn);
if (isProcedure == true)
{
cmd.CommandType = CommandType.StoredProcedure;
}
if (pms != null)
{
cmd.Parameters.AddRange(pms);
}
conn.Open();
return(cmd.ExecuteNonQuery());
}
}
/// <summary>
/// 执行命令得到一个SqlDataReader对象,你可以用这个对象的Reader对象读取数据----使用后需要Close()释放
/// </summary>
/// <param name="sql">sql语句或过程名</param>
/// <param name="isProcedure">是否是过程</param>
/// <param name="pms">执行所要的参数</param>
/// <returns></returns>
public static SqlDataReader GetReader(string sql, bool isProcedure, params SqlParameter[] pms)
{
string connectionStr = SqlConnectionPool.GetConnectionString(SqlConnectionPool.SqlConnectionType.Read);
SqlConnection conn = new SqlConnection(connectionStr);
SqlCommand cmd = new SqlCommand(sql, conn);
if (isProcedure == true)
{
cmd.CommandType = CommandType.StoredProcedure;
}
if (pms != null)
{
cmd.Parameters.AddRange(pms);
}
conn.Open();
//***CommandBehavior.CloseConnection 在sdr执行命令完毕后的行为是自动关闭连接,也就意味着手动调用sdr.Close()方法时，conn也close()***
SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
//bool b = sdr.Read();
//conn.Close();
return(sdr);
}
/// <summary>
/// 执行sql语句或存储过程返回一个表
/// sql=select * from student where sex=@a
/// sql2=select * from grade where score >@x and stuid>@n
/// </summary>
/// <param name="sql">sql语句或过程名</param>
/// <param name="isProcedure">是否是过程</param>
/// <param name="pms">执行所要的参数</param>
/// <returns></returns>
public static DataTable GetTable(string sql, bool isProcedure, params SqlParameter[] pms)
{
string connectionStr = SqlConnectionPool.GetConnectionString(SqlConnectionPool.SqlConnectionType.Read);
using (SqlConnection conn = new SqlConnection(connectionStr)) //using创建的对象，自动释放资源
{
SqlCommand cmd = new SqlCommand(sql, conn);
if (isProcedure == true)
{
cmd.CommandType = CommandType.StoredProcedure;
}
if (pms != null)
{
cmd.Parameters.AddRange(pms);
}
conn.Open();
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
sda.Fill(ds, "aa");
DataTable dt = ds.Tables["aa"];
return(dt);
}
}
static void Main(string[] args)
{
// 配置数据库连接
var configuration = new ConfigurationBuilder()
.AddJsonFile("appsettings.json")
.Build();
SqlConnectionPool.Init(configuration["ConnectionsStr:Write"], configuration.GetSection("ConnectionsStr").GetSection("Read").GetChildren().Select(a => a.Value).ToList());
{
var blogDal = new MyORM <Blogs>();
for (int i = 0; i < 10; i++)
{
var blog = blogDal.Find(1);
}
}
{
var blogDal = new MyORM <Blogs>();
blogDal.Insert(new Blogs
{
Name = "测试ORM插入",
CreateTime = DateTime.Now,
ModifiedTime = DateTime.Now,
Url = "www.google.com",
IsDeleted = false
});
//for (int i = 1; i < 100; i++)
//{
// var blog = blogDal.Find(1004);
// if (blog == null)
// Console.WriteLine($"第{i}未获取到");
// else
// break;
// Thread.Sleep(500);
//}
//Console.WriteLine("获取到了");
}
{
//var blogDal = new MyORM<Blogs>();
//var blog = blogDal.Find(2);
//blog.Name += "123";
//blogDal.Update(blog);
}
{
//var blogDal = new MyORM<Blogs>();
//blogDal.Delete(2);
}
{
//var blogDal = new MyORM<Blogs>();
//var blog = blogDal.Find(3);
//blog.Name += "123";
//string name = "123";
//blogDal.UpdateCondition(new Blogs { IsDeleted = true }, a => a.Name == name && a.IsDeleted == true);
}
{
//var name = Console.ReadLine();
//var isDelete = Console.ReadLine() == "Y";
//var query = new MyORM<Blogs>().Table;
//if (!string.IsNullOrEmpty(name))
// query = query.Where(a => a.Name == name);
//query = query.Where(a => a.IsDeleted == isDelete);
//var list = query.ToList();
//foreach (var item in list)
//{
// Console.WriteLine(item.Name + "-" + item.CreateTime);
//}
}
Console.ReadKey();
}
public DotsDataSource(SqlDBCredentials credentials)
{
DB = SqlConnectionPool.Instance(credentials).GetDataSource();
}