c# - 在 C# 中生成序列号

Question

我正在使用 C# 在 ASP.Net 上工作，我想生成一个应该像这样的序列 id:

ELG0001 , ELG0002, ...

ELG是PREFIX，0001应该是顺序

我使用的是sql server 2005

此 ID 将生成并添加到我的数据库中。我该怎么做？

你能帮我写代码吗？

Answer 1

使用这段代码我们可以简单地做到这一点

public string CJ()
    {
        string Id = GenerateId("cust", "cust_id", 6, "ELG", true);
        return Id;
    }
    public string GenerateId(string TableName, string ColumnName, int ColumnLength, string Prefix, bool Padding)
    {
        string Query, con, Id;
        con = "Data Source=CJ\\SQLEXPRESS;Initial Catalog=seq;Persist Security Info=True;User ID=sa;Password=123";
        SqlConnection cn = new SqlConnection(con);
        int preLength,padLength;
        preLength = Convert.ToInt32(Prefix.Length);
        padLength = ColumnLength - preLength;
        if (Padding == true )
        {
             Query = "SELECT '" + Prefix + "' + REPLACE(STR(MAX(CAST(SUBSTRING(" + ColumnName + "," + Convert.ToString(preLength + 1) + "," + padLength + ") AS INTEGER))+1," + padLength + "),' ',0) FROM " + TableName;

        }
        else
        {
            Query = "SELECT '" + Prefix + "' + CAST(MAX(CAST(SUBSTRING(" + ColumnName + "," + Convert.ToString(preLength + 1) + "," + padLength + ") AS INTEGER))+1 AS VARCHAR) FROM " + TableName;
        }
        SqlCommand com = new SqlCommand(Query, cn);
        cn.Open();
        if (com.ExecuteScalar().ToString() == "")
        {
            Id = Prefix;
            if (Padding == true)
            {
                for (int i = 1; i  padLength - 1; i++)
                {
                    Id += "0";
                }
            }
            Id += "1";
        }
        else
        {
            Id = Convert.ToString(com.ExecuteScalar());
        }
        cn.Close();
        return Id;
}

谢谢你的帮助只需添加方法 CJ()正如我在这里所做的那样

protected void Button1_Click(object sender, EventArgs e)
    {
        string con;
        con = "Data Source=CJ\\SQLEXPRESS;Initial Catalog=seq;Persist Security Info=True;User ID=sa;Password=123";
        using (SqlConnection cn = new SqlConnection(con))
        {
            cn.Open();
            using(SqlTransaction trans = cn.BeginTransaction())
            using (SqlCommand cmd = cn.CreateCommand())
            {
                cmd.Transaction = trans;
                cmd.CommandText = "INSERT INTO cust([cust_id],[cust_name],[cust_add]) VALUES(@cust_id,@cust_name,@cust_add)";
                cmd.Parameters.Add("@cust_id",CJ());
                cmd.Parameters.Add("@cust_name",TextBox1.Text);
                cmd.Parameters.Add("@cust_add",TextBox2.Text);
                cmd.ExecuteNonQuery();
                trans.COmmit();
            }
            cn.Close();
            Response.Write("alert('DATA SAVED')");
            TextBox1.Text = "";
            TextBox2.Text = "";
        }

   }