C# ACCESS的SQL添加数据操作封装类AccessSqlIn

C# ACCESS的SQL添加数据操作封装类AccessSqlIn

找了很久，都没有找到C# 的Access数据添加操作封装的类，又不想用添加别人的lib库，只好自己封装了一个。
该类采用简化设计，没有异常处理，只是生成SQL时会判断下条件，如果条件不符就会返回空字符，要自己写判断条件就行了。

using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace kqgz.Model
{
    class AccessSqlIn
    {
        private string _tbname = "";
        public string TbName { get => _tbname; set => _tbname = value; }

        private Hashtable slist = new Hashtable();        
        public void AddString(string key,string value)
        {
            if(slist.ContainsKey(key))
            {
                slist.Remove(key);
            }

            slist.Add(key, value);
        }

        private Hashtable dlist = new Hashtable();
        public void AddDate(string key,string value)
        {
            if (dlist.ContainsKey(key))
            {
                dlist.Remove(key);
            }

            dlist.Add(key, value);
        }

        private Hashtable nlist = new Hashtable();
        public void AddNull(string key)
        {
            if (nlist.ContainsKey(key))
            {
                nlist.Remove(key);
            }

            nlist.Add(key, null);
        }

        private Hashtable ilist = new Hashtable();
        public void AddInt(string key, int value)
        {
            if (ilist.ContainsKey(key))
            {
                ilist.Remove(key);
            }

            ilist.Add(key, value);
        }

        public string GetInsertSql()
        {
            string sql = "";

            if(_tbname == "")
            {
                return "";
            }

            int slen = slist.Count;
            int dlen = dlist.Count;
            int nlen = nlist.Count;

            if(slen == 0 && dlen ==0 && nlen == 0)
            {
                return "";
            }

            sql = "insert into {0}({1}) values({2})";

            sql = sql.Replace("{0}", _tbname);

            string liestr = "";
            string vstr = "";

            //string
            foreach (DictionaryEntry de in slist) 
            {
                if(liestr == "")
                {
                    liestr = de.Key.ToString();
                }
                else
                {
                    liestr += "," + de.Key.ToString();
                }

                if (vstr == "")
                {
                    vstr = "'" + de.Value.ToString() + "'";
                }
                else
                {
                    vstr += ",'" + de.Value.ToString() + "'";
                }

            }

            //date
            foreach (DictionaryEntry de in dlist)
            {
                if (liestr == "")
                {
                    liestr = de.Key.ToString();
                }
                else
                {
                    liestr += "," + de.Key.ToString();
                }

                if (vstr == "")
                {
                    vstr = "#" + de.Value.ToString() + "#";
                }
                else
                {
                    vstr += ",#" + de.Value.ToString() + "#";
                }
            }

            //null
            foreach (DictionaryEntry de in nlist)
            {
                if (liestr == "")
                {
                    liestr = de.Key.ToString();
                }
                else
                {
                    liestr += "," + de.Key.ToString();
                }

                if (vstr == "")
                {
                    vstr = "null";
                }
                else
                {
                    vstr += ",null";
                }
            }

            //int
            foreach (DictionaryEntry de in ilist)
            {
                if (liestr == "")
                {
                    liestr = de.Key.ToString();
                }
                else
                {
                    liestr += "," + de.Key.ToString();
                }

                if (vstr == "")
                {
                    vstr = Convert.ToString(de.Value);
                }
                else
                {
                    vstr += "," + Convert.ToString(de.Value);
                }
            }

            sql = sql.Replace("{1}",liestr);
            sql = sql.Replace("{2}",vstr);

            return sql;
        }

        public void ResetData()
        {
            slist.Clear();
            dlist.Clear();
            nlist.Clear();
            ilist.Clear();
        }

        public void ResetTable()
        {
            TbName = "";
        }

    }
}

一边判断条件一边添加字段就行了，多方便，从此过上幸福的日子，不需要对着一长串insert语句发傻，不担心字段和数值错位。
我只封装了自己用到的数据类型，float、double那些没有封装，需要其他类型的话可以自己照样子添加就行了。

使用方法如下：

AccessSqlIn sqlin = new AccessSqlIn();
sqlin.TbName = "userinfo";
//字符串
sqlin.AddString("uno",ghstr);
//日期
string rzstr = dateTimePicker1.Value.ToString("yyyy-M-d");
sqlin.AddDate("uinday", rzstr);
//整型
string jtstr = tbjintie.Text.Trim();
int jt = Convert.ToInt32(jtstr);
sqlin.AddInt("ujt", jt);

//null
sqlin.AddNull("ulfday");

string sql = sqlin.GetInsertSql();
if(sql == "")
{
    MessageBox.Show("SQL出现异常", "提示");
    return;
}

添加成功后，调用ResetData()，又可以继续添加下一条记录，真过瘾。