SQL 随笔

if not exists () insert into table value else update talbe set  //若存在更新不存在插入

 

select isnull(max( cast( substring(" + colName + ",patindex('%[0-9]%'," + colName + "),50) as float)),0) + 1  as num from " + tableName + " //用来获取某一列得自证值。可处理字母加自增数字得/

我写得帮助类：

public class DBCreateWareId
    {
        public string CreateId(string tableName, string colName) //tableName表名/colName列名/返回id加1；如2
        {
            return GetId(tableName, colName);
        }
        public string CreateId(string tableName, string colName, string str)//tableName表名/colName列名/str字段前面的字母如DBC001中的DBC/返回id加1,如BB2；
        {
            return str + GetId(tableName, colName);
        }
        public string CreateId(string tableName, string colName, string str, bool date)//tableName表名/colName列名/str字段前面的字母如DBC001中的DBC/返回id加1,如BB2017010011122；
        {
            return str + DateTime.Now.ToString("yyyyMMdd") + GetId(tableName, colName);
        }

        private string GetId(string tableName, string colName)
        {
            string id = "";
            string Sql = "select isnull(max( cast( substring(" + colName + ",patindex('%[0-9]%'," + colName + "),50) as float)),0) + 1  as num from " + tableName + "";

            using (SqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, Sql))
            {
                while (rdr.Read())
                {
                    id = rdr["num"].ToString();
                }
                if (id == "")
                {
                    id = "0";
                }
            }
            return id;
        }
    }