如何在asp.net中生成带前缀的连续序列号 [英] How to generate continuous sequence number with a prefix in asp.net

问题描述

我想生成带有前缀PB的6位序列号...

我找到了一个代码项目中的解决方案但显示错误...

  public   string  serialId（）
 {
  string  Id = GenerateId（  customer_details，  id， 6 ， < span class =code-string> PB， true ）; 
  return  Id; 
} 
 
  public   string  GenerateId（ string  TableName， string  ColumnName， int  ColumnLength， string 前缀， bool 填充）
 {
  string  query，con，Id; 
 con =   Data Source = .; Initial Catalog = login_; Integrated Security = True ; 
 SqlConnection cn =  new  SqlConnection（con）; 
  int  preLength，padLength; 
 preLength = Convert.ToInt32（Prefix.Length）; 
 padLength = ColumnLength  -  preLength; 
  if （填充==  true ）
 {
 query =   SELECT' +前缀+  '+ REPLACE（STR（MAX（CAST（SUBSTRING（ + ColumnName +  ， + Convert.ToString（preLength +  1 ）+  ， + padLength +  ）AS INTEGER））+ 1， + padLength +  ），''，0）FROM + TableName ; 
} 
 其他 
 {
 query =   SELECT' +前缀+  '+ 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（）==  < span class =code-string>） 
 {
 Id = Prefix; 
  if （填充==  true ）
 {
  for （ int  i =  1 ; i <  = padLength  -   1 ; i ++）
 {
 Id + =   0; 
} 
} 
 Id + =   1; 
} 
  else  
 {
 Id = Convert.ToString（com.ExecuteScalar（））; 
} 
 cn.Close（）; 
  return  Id; 
} 

在上面的代码中我调试时会在上面的粗线中引发异常...

例外是无效的列名id...



有人可以帮我解决这个问题吗？



问候，

Sajin A



我的尝试：



我已经尝试过多次调试和更改代码

解决方案

而不是弄乱它，给你的table一个标识值（将提供代码的数字部分），然后添加一个Computed列： https://technet.microsoft.com/en-GB/library/ms191250%28v=sql.105%29.aspx?f=255&MSPPError=- 2147217396 [ ^ ]

这将为您维护代码，就好像它是一个单独的列一样，而不必像对待显示的代码一样冒险进行SQL注入。

永远不要连接字符串来构建SQL命令。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏您的整个数据库。请改用参数化查询。

我想你会发现这个问题很有意思如何创建自定义主键？ [ ^ ]和来自Maciej Los的答案，主要是链接。

使用SQL Server自定义自动生成的序列 - SQLTeam.com [ ^ ]

  string  id =  string  .Format（< span class =code-string>  PT {0}，sequenceNumber.ToString（）。PadLeft（ 13 ，'  0'））; 

Hi,

I want to generate a 6 digit sequence number with a prefix PB...
I have a found a solution in code project but its showing error...

public string serialId()
        {
            string Id = GenerateId("customer_details", "id", 6, "PB", true);
            return Id;
        }

        public string GenerateId(string TableName, string ColumnName, int ColumnLength, string Prefix, bool Padding)
        {
            string query, con, Id;
            con = "Data Source=.;Initial Catalog=login_;Integrated Security=True";
            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;
        }

In the above code while I debug it throws an exception in the above bold line...
The exception is "invalid column name id"...

can anyone help me in clearing this problem?

Regards,
Sajin A

What I have tried:

I have tried debugging and changing the code many times

解决方案

Instead of messing around with that, give your table an Identity value (that will provide the numeric part of your code) and then add a Computed column: https://technet.microsoft.com/en-GB/library/ms191250%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396[^]
This will maintain the code for you as if it was a single column without you having to risk SQL Injection as you do with the code you show.
Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.

I think you will find this question interesting How do i create a custom primary key?[^] and the answer from Maciej Los which is mainly a link.
Custom Auto-Generated Sequences with SQL Server - SQLTeam.com[^]

string id = string.Format("PT{0}", sequenceNumber.ToString().PadLeft(13, '0'));

这篇关于如何在asp.net中生成带前缀的连续序列号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！