生成流水单号

1、再数据库写存储过程

2、c#调用存储过程。

3、格式化调用的信息。 

 

存储过程如下： 

CREATE proc [dbo].[JG_GetIdMax]  --获取当天的流水号
(@TabableName varchar(50) = '[Jixiexinxi]',
@IdBegin   varchar(50),
@VarID  varchar(20)
)
as  
set nocount on;  --不返回计数，提高应用程序性能
declare @sql varchar(200)
set @sql='select top 1  RIGHT('+@VarID+',5)+1 as id from '+@TabableName +' where '+@VarID+' LIKE '''+@IdBegin+''' ORDER BY '+@VarID+' DESC'
--print(@sql)
 exec(@sql)
GO

调用语句   

EXEC [JG_GetIdMax](@TabableName = N'TabableName',@IdBegin = N'190819',@VarID = N'1')

 

C# 读取存储过程

   #region 存储过程 
        /// <summary> 
        /// 执行存储过程，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close ) 
        /// </summary> 
        /// <param name="storedProcName">存储过程名</param> 
        /// <param name="parameters">存储过程参数</param> 
        /// <returns>SqlDataReader</returns> 
        public static int RunProcedure(string storedProcName, IDataParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(ConnStr))
            { 
                SqlDataReader returnReader;
                conn.Open();
                SqlCommand cmd = BuildQueryCommand(conn, storedProcName, parameters);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                returnReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                int bb = 0;
                while (returnReader.Read())
                {
                    bb = (int)returnReader[0]; 
                }
                conn.Close();
                return bb;
            }
        }


        /// <summary> 
        /// 执行存储过程，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close ) 
        /// </summary> 
        /// <param name="storedProcName">存储过程名</param> 
        /// <param name="parameters">存储过程参数</param> 
        /// <returns>SqlDataReader</returns> 
        public static SqlDataReader RunProcedure2(string storedProcName, IDataParameter[] parameters)
        {
            SqlConnection connection = new SqlConnection(ConnStr);
            SqlDataReader returnReader;
            connection.Open();
            SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
            command.CommandType = CommandType.StoredProcedure;
            returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);

            connection.Close();
            return returnReader;

        }
        private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = new SqlCommand(storedProcName, connection);
            command.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter parameter in parameters)
            {
                if (parameter != null)
                {
                    // 检查未分配值的输出参数,将其分配以DBNull.Value. 
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    command.Parameters.Add(parameter);
                }
            }
            return command;
        }

        #endregion

 

 调用格式化

  string IdBegin = DateTime.Now.ToString("yyMMdd");
            string organ = System.Configuration.ConfigurationManager.AppSettings["xuChangOrgan"];
            IDataParameter[] iData = new SqlParameter[3];
            iData[0] = new SqlParameter("@TabableName", TabableName);
            iData[1] = new SqlParameter("@IdBegin", organ+IdBegin + "%");
            iData[2] = new SqlParameter("@VarID", VarID);
            int num = SqlHandler.RunProcedure("JG_GetIdMax", iData);
            string aa;
            if (num > 0)
            {
                num = 100000 + num;
                aa = num.ToString();
                aa = aa.Substring(aa.Length - 5, 5);
            }
            else
            {
                aa = "00001";
            }
            return organ + IdBegin + aa;