예제 #1
0
        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();
                        }
                    }
                }
            }
        }
예제 #2
0
            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);
            }
예제 #3
0
#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();
        }
예제 #4
0
        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);
        }
예제 #5
0
        /*
         * 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();
        }
예제 #6
0
        /// <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());
            }
        }
예제 #7
0
        /// <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);
        }
예제 #8
0
        /// <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);
            }
        }
예제 #9
0
        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();
        }
예제 #10
0
 public DotsDataSource(SqlDBCredentials credentials)
 {
     DB = SqlConnectionPool.Instance(credentials).GetDataSource();
 }