澳门美高梅手机网站长年累月前写的3个Access实体类生产工具

有时候翻到在此从前写的小玩意儿,数据表实体类生成!只写了Access数据库,等未来有时光试着看看写三个匹配市面主流数据库的!

缓存是一种服务端常用的优化策略,紧要用来对数据库进行并发读爱惜,从而压实系统吞吐量。

代码如下:

1 使用境况

 static class Program
    {
        /// <summary>
        /// 应用程序的主入口点。
        /// </summary>
        [STAThread]
        static void Main()
        {
            Application.EnableVisualStyles();
            Application.SetCompatibleTextRenderingDefault(false);
            Application.Run(new Form1());
        }
    }

怎么样动静下适合采取缓存?
  • 短期内一样数量被重复查询且更新频率不高,这种气象可以设想拔取缓存。应用先查询缓存,假如查询不到数码,则从数据库加载该数额并保留到缓存;
  • 高并发热点数据,完全透传到数据库会招致数据库宕机,必须接纳缓存;

上边是主窗体代码:

怎么景况下不提出选择缓存?
  • 配置型数据在不频仍读的地方下(数据库url等),可以不使用缓存。那类数据提议采纳布署基本的措施积极推送;
  • 实时性要求较高的数额,不指出利用缓存。比如用户密码,一般只用来做登录校验,不会一再读取,缓存该数量并不能大幅升级系统品质,还或然导致用户密码修改后不可以登时生效从而影响用户体验(缓存更新战败的动静);

 public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        #region 全部变量定义
        bool Canmove = false;
        Point Pf;
       // string ConnectionString = “”;
        #endregion
        void WriteLog(string logstr)
        {
            textBox2.Text += logstr+”\r\n”;
        }
        #region 测试数据库是不是能连上
        void TestConnection(string str)
        {
            using (OleDbConnection con = new OleDbConnection(str))
            {
                textBox2.ResetText();
                try
                {
                    WriteLog(“尝试打开数据库链接……”);
                    con.Open();
                    WriteLog(“数据库链接测试成功!”);
                }
                catch (Exception ex)
                {
                    WriteLog(“数据库链接测试失利:”+ex.Message);
                }
                finally
                {
                    WriteLog(“关闭数据库链接….”);
                    con.Close();
                }
            }

2 缓存选型

支付中常用的缓存有地面缓存和分布式缓存。可以用HashMap自身落成多少个简约的地头缓存,也足以用Guava
cache大概ehcache来完毕。对于分布式缓存,业界常用的有redis、memcached、还有阿里的tair等。

        }
        #endregion
        #region 取得数据库的表名
        string[] GetTableNames(string str)
        {
            using (OleDbConnection con = new OleDbConnection(str))
            {
                DataTable dt = new DataTable();
               
                    try
                    {
                        con.Open();
                        WriteLog(“初始从数据库得到全体表名!”);
                        dt =
con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new Object[] { null,
null, null, “table” });
                        int n = dt.Rows.Count;
                        string[] strTable = new string[n];
                        int m = dt.Columns.IndexOf(“TABLE_NAME”);
                        for (int i = 0; i < n; i++)
                        {
                            DataRow m_DataRow = dt.Rows[i];
                            strTable[i] =
m_DataRow.ItemArray.GetValue(m).ToString();
                        }
                        WriteLog(“成功得到富有表名!”);
                        return strTable;
                       
                    }
                    catch (Exception ex)
                    {
                        WriteLog(“获取表名出错:”+ex.Message);
                        return null;
                    }
                    finally
                    {
                        con.Close();
                        dt.Dispose();
                    }
                }
           
        }
        #endregion
        #region 依照表名取得表中每一个字段及相应数据类型
        Dictionary<string ,string > GetCoulmNams(string
constr,string TableName)
        {
            using (OleDbConnection con = new OleDbConnection(constr))
            {
                DataTable dt = new DataTable();
                try
                {
                   
WriteLog(“按照表名称拿到具有字段及其对应的数据类型!”);
                    con.Open();
                  
                    dt =
con.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new Object[] { null,
null, TableName, null });
                   // dataGridView1.DataSource = dt;
                    int n = dt.Rows.Count;
                    Dictionary<string, string> CoulnmsInfo = new
Dictionary<string, string>();
                    int m = dt.Columns.IndexOf(“COLUMN_NAME”);
                    int j = dt.Columns.IndexOf(“DATA_TYPE”);
                   
                    for ( int i = 0; i < n; i++)
                    {
                        DataRow m_DataRow = dt.Rows[i];
                       
CoulnmsInfo.Add(m_DataRow.ItemArray.GetValue(m).ToString(),GetType((int)m_DataRow.ItemArray.GetValue(j)));
                        WriteLog(“获取字段” +
m_DataRow.ItemArray.GetValue(m).ToString() + “的类型码成功!”);
                    }
                    return CoulnmsInfo;
                }
                catch (Exception ex)
                {
                    WriteLog(“获取字段类型出错:”+ex.Message);
                    return null;
                }
                finally
                {
                    con.Close();
                    dt.Dispose();
                }
            }
        }
#endregion
        #region 判断字段是还是不是为主键
        bool IsprimaryKeys(string tbname,string Name)
        {
            OleDbConnection connection = new
OleDbConnection(textBox1.Text.Trim());
            connection.Open();
           // DataTable schemaColumns =
connection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new string[] {
null, null,tbname, null });
            DataTable primaryKeys =
connection.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, new
string[] { null, null, tbname });
            connection.Close();
            int j = -1;
            foreach (DataRow row in primaryKeys.Rows)
            {
                string fname=row[“COLUMN_NAME”].ToString();
                if (fname  == Name)
                {
                    j++;
                }
                            
             }
            if (j>=0)
            { return true; }
            else
            {
                return false;
            }
           
        }
        #endregion
        #region 数据库类型与C#中类型转换
        string  GetType(int num)
        {
          
            switch (num)
            {
              
                case 2:
                    return “Int16”;
                case 3:
                    return “int”;
                case 4:
                    return “Single”;
                   
                case 5:
                    return “Double”;
                case 6:
                case 131:
                    return “decimal”;
                case 7:
                    return “DateTime”;
                case 11:
                    return “bool”;

怎么着动静切合当地缓存?
  • 往往被访问的大目的。分布式缓存须要跨进度访问数据,缓存服务器必要先种类化该目的,通过互联网传输到应用服务器,然后应用服务器再反连串化,而连串化与反种类化的历程是拾分消耗CPU的操作。
  • 数据量小的单机应用;

                case 18:
                    return “byte”;
                case 130:
                    return “string”;
                case 128:
                    return “Binary”;
                case 129:
                    return “char”;
                case 0:
                case 8: 
                case 9:
                case 10:
                case 12:
                case 13:
                case 14:
                case 15:
                case 16:
                case 17:
                case 19:
                case 20:
                case 21:
                case 64:
                case 72:
                case 132:
                case 133:
                case 134:
                case 135:
                case 136:
                case 138:
                case 139:
                case 200:
                case 203:
                case 204:
                case 205:
                    return “”;

怎么样状态符合分布式缓存?
  • 天命据量的缓存,数据频仍的增强又清空(本地缓存会招致频繁的废料回收);
  • 分布式应用,八个使用实例访问同一份缓存数据;

                default: return null ;
                   
                   
            }
         
        }
       #endregion
        #region 写文件
        void WriteFile(string NameSpaces,string FileName1,string
FileName2,IDbConnection dbconnect)
        {
            if (File.Exists(FileName1  + “.cs”))
            {
                File.Delete(FileName1 + “.cs”);
            }
            if (File.Exists(FileName2 + “.cs”))
            {
                File.Delete(FileName2 + “.cs”);
            }
            WriteLog(“创设文件!”);
            FileInfo f = new FileInfo(FileName1 + “.cs”);
            FileInfo f1=new FileInfo (FileName2 + “.cs”);
            StreamWriter sw1 = f1.AppendText();
            StreamWriter sw = f.AppendText();
            try
            {
                WriteLog(“一.写文件头:”);
                WriteLog(“1.写DB类文件头!”);
                sw1.WriteLine(“using System.Data;”);
                sw1.WriteLine(“using System.Data.Linq;”);
                sw1.WriteLine(“using System.Data.OleDb;”);
                sw1.WriteLine(“using System.Diagnostics;”);
                sw1.WriteLine(“namespace “+NameSpaces );
                sw1.WriteLine(“{“);
                sw1.WriteLine(“public class DB”);
                sw1.WriteLine(“{“);
                sw1.WriteLine(“DataContext dc;”);
                WriteLog(“2.写实体类文件头!”);
                sw.WriteLine(“using System.Data.Linq.Mapping;”);
                sw.WriteLine(“namespace\t”+NameSpaces );
                sw.WriteLine (“{“);
                //一下依据表生产对应的类,依照表中的字段生产对应属性;
                string[] tables =
GetTableNames(textBox1.Text.Trim());
                int l = 1;
                WriteLog(“二.写文件内容:”);
                foreach (string str in tables)
                {
                    WriteLog(“发轫写第” + l +
“张表(“+str+”)的实体类!”);
                               
                    //按照注脚写类名
                    sw.WriteLine(“[Table (Name=\””+str+”\”)]”);
                    sw.WriteLine(“public class ” + str);
                    sw.WriteLine(“{“);
                    Dictionary<string, string> di =
GetCoulmNams(textBox1.Text, str);
                    foreach (KeyValuePair<string, string> value in
di)
                    {
                       

3 数据数据更新

因为缓存和数据库是七个单身的零件,两者不可以在同一个地面工作内做到换代,所以在写的时候有或然会冒出数量不一样的情状。不考虑分布式事务的前提下,有上边三种更新策略:

                        WriteLog(“开端写字段:” + value.Key );
                        if (IsprimaryKeys(str, value.Key) == true)
                        {
                            sw.WriteLine(“[Column(IsPrimaryKey
=true)]”);
                        }
                        else
                        {
                            sw.WriteLine(“[Column]”);
                        }
                        sw.WriteLine(“public ” + value.Value + ” ” +
value.Key + “{get;set;}”);
                                         

先删除缓存,再次创下新数据库
  • case1:低并发场景下,先删除缓存,更新数据库后,后续读操作会从数据库读取数据并立异缓存,仅会导致一回缓存未命中;
  • case2:高并发场景下,写线程T1先删除缓存数据,读线程T2未命中缓存,从数据库读取数据并加载到缓存,然后线程T1翻新数据库数据。那时缓存中的值就是本来的老多少,后续读操作都属于脏读。

                    }
                    sw.WriteLine(“}”);
                    WriteLog(“写第” + l + “张表的阐发!”);
                    sw1.WriteLine(“public Table<” + str + “>” +
str + “;”);
                    l++;      

先更新数据库,再删除缓存
  • case3:先更新数据库,再删除缓存,在剔除缓存失利的动静下,也会并发脏数据;
  • case4:缓存数据失效的须臾间,读线程T1未命中缓存,然后到数据库取多少,取完数据后来了个写线程T2,T2先往数据库写多少,然后删除缓存,接着T1再把读到的老多少加载到缓存,此时也会导致脏读。这一个状态出现的可能率分外低,须求缓存失效的弹指间有出现的读写操作。而且数据库的写会比读慢很多,并且须求对数码加锁,所以T1必须在T2此前进入数据库举办操作,又要晚于T2更新缓存,全部那几个规则都持有的几率万分低。

                }
               

先更新数据库,再立异缓存
  • case5:要是七个写线程T壹,T2并发立异某一数目,T1先拿到锁、更新数据库并且释放锁,T2获取锁、更新数据库然后放走锁;接下去T2更新缓存值,T1更新缓存值,那时候缓存中的数据T1为脏数据。

不考虑缓存和数据库操作退步的状态,case4暴发脏数据的票房价值很小。再接合缓存数据的机关过期,该方案适合成为缓存更新的通用策略。

                WriteLog(“写DB类的起首化方法!”);
                sw1.WriteLine(“public DB(IDbConnection con) “);
                sw1.WriteLine(“{“);
                sw1.WriteLine(“dc = new DataContext(con);”);
                int n=1;
                foreach(string str in tables)
                {
                    WriteLog(“建立第” + n + “张表的Object对象!”);
                    sw1.WriteLine(str
+”=dc.GetTable<“+str+”>();”);
                    n++;
                }
                WriteLog(“三.写文件尾:”);
                sw.WriteLine(“}”);
                sw1.WriteLine(“}”);
                sw1.WriteLine(“}”);
                sw1.WriteLine(“}”);
                WriteLog(“生成文书成功!”);
                ProcessStartInfo psi = new ProcessStartInfo();
                psi.FileName = “explorer”;
                psi.Arguments = “/e,/select,” + Application.StartupPath

4 缓存击穿

缓存击穿可以分为以下二种状态:

  • FileName1+”.cs”;
                    Process.Start(psi);
                  
查询数据库中不设有的值,每一遍都会造访数据库,即便有人恶意攻击则会对数据库造成很大压力。

缓解方案:

  • 对于数据库中不设有的key-value查询,在缓存中为该key设置三个暗许value。该value必要持有强烈特点,不可以苦恼符合规律工作(比如”NULL”);
  • 行使布隆过滤器,一大半在数据库中不存在的询问请求会被该过滤器直接过滤,不会透传到数量库层。

            }
                catch (Exception ex)
            {
                    WriteLog(“生成文书发出错误:”+ex.Message);
             }
            finally
            {
                sw.Close();
                sw1.Close();
            }
        }

数量缓存时间到期,失效弹指间多个线程/进度查询数据,则各种请求都会执行查询数据库,然后设置缓存的动作。如果该查询是热门key查询,数据库压力会须臾间增大。

缓解方案:

  • 恳请查询数据库前先拿到分布式互斥锁,取得锁的进度A查询数据库并设置缓存中的值,其他线程等待线程A执行落成后从缓存中读取值。

public String getValue(String key) {
    String retValue = getValueFromCache(key);

    if(null != retValue) {
        return retValue;
    }

    lock = getMutexLock(key);
    try {       
        retValue = getValueFromCache(key);
        if(null == retValue) {
            retValue = getValueFromDB(key);
            setCache(key, retValue);
        }   
    }   
    finally {
        unlock(key);
    }

    return retValue;
}
  • 若果使用当地缓存,可以用guava代替上述作用,guava默许协助三多线程的产出访问。

Cache<Key, Object> cache = CacheBuilder.newBuilder().maximumSize(1000).build();

try {
    cache.get(key, new Callable<Key, Object>() {
        @Override
        public Object call() throws AnyException {
            return getFromDB(key);
        }
    });
} catch (ExecutionException e) {
    throw new OtherException(e.getCause());
}

        #endregion
        #region 测试链接按钮单击事件
        private void button1_Click(object sender, EventArgs e)
        {
            WriteLog(“测试链接按钮单击”);
            TestConnection(textBox1.Text.Trim());
        }
        #endregion
        #region 生成.cs文件按钮单击事件
        private void button2_Click(object sender, EventArgs e)
        {
            textBox2.ResetText();
            WriteLog(“生成.cs文件按钮单击”);
            string namespaces = Interaction.InputBox(“请输入命名空间!”,
“输入命名空间”, “gaofajin”,Control.MousePosition.X
-200,Control.MousePosition.Y-100);
            if (namespaces.Length == 0)
            {
                WriteLog(“没有输入命名空间,生成文书操作不可能持续!”);
                return;
            }
            string filename1 =
Interaction.InputBox(“请输入实体类文件名称!”, “输入文件名”,
“gaofajinmodel”, Control.MousePosition.X-200,
Control.MousePosition.Y-100);
            if (namespaces.Length == 0)
            {
               
WriteLog(“没有输入实体类文件名!生成文书操作不能持续!”);
                return;
            }
            string filename2 =
Interaction.InputBox(“请输入DB类文件名称!”, “输入文件名”, “gaofajinDB”,
Control.MousePosition.X -200, Control.MousePosition.Y-100);
            if (namespaces.Length == 0)
            {
               
WriteLog(“没有输入DB类文件名!生成文书操作不可以继续!”);
                return;
            }
            IDbConnection c = new
OleDbConnection(textBox1.Text.Trim());
            WriteFile(namespaces,filename1,filename2,c);      
          
        }
        #endregion
        #region 退出程序按钮单机时间
        private void button3_Click(object sender, EventArgs e)
        {
            Close();
        }
        #endregion
        #region 使无边框的窗体可以拖动
        private void Form1_MouseMove(object sender, MouseEventArgs e)
        {
            if (Canmove == true && e.Button == MouseButtons.Left)
            {
                Point p = Control.MousePosition;
                p.Offset(Pf);
                Location = p;

5 热点key

作业高峰期,多量伸手访问同一份数据,hash算法使得一样key的有着流量涌向同一台机械,那台机械成为系统瓶颈,该问题的挑衅在于它不可以透过增添机械容积来缓解。
杀鸡取卵热点难点最管用的法子是从业务角度将走俏拆分,对于有些无法拆分的事务,可以运用多份数据的情势分担负载。那类方案经过捐躯多少一致性来升高效用。

            }
        }

客户端本地缓存

人心向背数据缓存在客户端本地,并设置贰个较短的过期时间,每一回读请求先反省本地缓存。本地缓存中有数量则直接再次来到;假使无法打中本地缓存,再去做客分布式缓存服务器。该方案的先天不足是分布式缓存数据更改后不可以及时刷新当地缓存,造成数据脏读,如若工作可以容忍短期内数据不精确,可以使用本方案。

        private void Form1_MouseUp(object sender, MouseEventArgs e)
        {
            Canmove = false;
        }

数据多读多写

将走俏key散列为四个子key,子key被hash到缓存集群的不比机器上,读数据的时候随机挑选多少个子key访问对应的缓存服务器,写多少的时候要创新该key对应的全部子key值。
只要有个别热门key被散列为N个子key,缓存服务器的数额M>N,最出色的事态下,缓存服务器压力会降低到单key的1/N。
该方案需求工作提前感知热点key或许利用具有统计hotkey能力的缓存服务器,同时革新N份缓存数据也设有有的战败的危机,业务须求忍受短期内的数据差距。

        private void Form1_MouseDown(object sender, MouseEventArgs e)
        {
            if (e.Button == MouseButtons.Left)
            {
                Canmove = true;
                Pf = new Point(-e.X, -e.Y);
            }
        }
        #endregion
        private void Form1_FormClosing(object sender,
FormClosingEventArgs e)
        {
            WriteLog(“程序正在被关闭!”);
            if (DialogResult.OK != MessageBox.Show(“明确退出程序吗?”,
“退出程序”, MessageBoxButtons.OKCancel, MessageBoxIcon.Question))
            {
                e.Cancel = true;
            }
            else { e.Cancel = false; }
        }

6 缓存预热

缓存预热是指在用户可访问服务从前,将走俏数据加载到缓存的操作,那样可以使得防止上线后须臾时大流量造成系统不可用。

参考:http://coolshell.cn/articles/17416.html

        private void button4_Click(object sender, EventArgs e)
        {
            textBox1.Text = ShowConnectDialog();
        }
        string ShowConnectDialog()
        {
            string ConnectionString = string.Empty;
            DataConnectionDialog dcd = new DataConnectionDialog();
            dcd.DataSources.Add(DataSource.AccessDataSource);
            dcd.DataSources.Add(DataSource.OdbcDataSource);
            dcd.DataSources.Add(DataSource.OracleDataSource);
            dcd.DataSources.Add(DataSource.SqlDataSource);
            dcd.DataSources.Add(DataSource.SqlFileDataSource);
           
            if (DialogResult.OK ==DataConnectionDialog.Show(dcd))
            {
                ConnectionString = dcd.ConnectionString;
            }
            return ConnectionString;
        }
    }

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注