关于计数以及Index返回订单号（不重复）（sqlserver &sqlite）

数据库表格操作：

USE [OrderManage2021]
GO

/****** Object:  Table [dbo].[OrderSIndex]    Script Date: 06/11/2021 17:37:50 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[OrderSIndex](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[OrderName] [nvarchar](50) NULL,
	[OrderIndexInMonth] [int] NULL,
	[CreateDate] [datetime] NULL,
	[Updated] [datetime] NULL,
	[ThisMonth] [nvarchar](20) NULL
) ON [PRIMARY]

GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'订单名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'OrderSIndex', @level2type=N'COLUMN',@level2name=N'OrderName'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'订单当前月Index' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'OrderSIndex', @level2type=N'COLUMN',@level2name=N'OrderIndexInMonth'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'当前月份（2021-02）' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'OrderSIndex', @level2type=N'COLUMN',@level2name=N'ThisMonth'
GO

EXEC sys.sp_addextendedproperty @name=N'Name', @value=N'当前所有订单的index值' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'OrderSIndex'
GO

ALTER TABLE [dbo].[OrderSIndex] ADD  CONSTRAINT [DF_OrderSIndex_CreateDate]  DEFAULT (getdate()) FOR [CreateDate]
GO

程序DAL操作：

 public   class DALOrderSIndex
    {
        string ThisMonth = $"{DateTime.Now.Year}{DateTime.Now.Month.ToString().PadLeft(2,'0')}"; 
        /// <summary>
        /// 插入一条或者更新一条 返回数据（当前更新的数据）
        /// </summary>
        /// <param name="OrderName"></param>
        /// <returns></returns>
        public string  InsertOrUpdate(string OrderName)
        {
            string sql = $@"declare @YearAndMonth nvarchar(10)
                           declare @OrderName nvarchar(20) 
                           set @YearAndMonth  = '{ThisMonth}'
                           set @OrderName =  '{OrderName}'
if exists(select 1 from OrderSIndex where OrderName = @OrderName and  ThisMonth = @YearAndMonth )---如果数据存在
begin
  update OrderSIndex set OrderIndexInMonth = OrderIndexInMonth+1 ,Updated = GETDATE() where OrderName = @OrderName and  ThisMonth = @YearAndMonth 
end 
else
begin
  insert  OrderSIndex ([OrderName] ,[OrderIndexInMonth],[ThisMonth]) values (@OrderName ,1,@YearAndMonth)
end
select ThisMonth + right('00000000000'+convert(varchar(4), OrderIndexInMonth),4) from OrderSIndex where OrderName = @OrderName and  ThisMonth = @YearAndMonth ";
            string  OutStr = DBUtility.DapperDbHelper.ExecuteScalar<string>(sql);
            return OutStr;
       }
 }

使用方法：

 string ThisOrderName = "OrderBaojia";//当前订单名称
 ModelIndex.BaojiaNo = $"BO{new DALOrderSIndex().InsertOrUpdate(ThisOrderName)}";
 //  BaojiandanNo.Text= $"BO{new DALSetNoIndex().GetTHisNoIndex(ThisOrderName,1,3)}";

SQLite

PRAGMA foreign_keys = false;

-- ----------------------------
-- Table structure for OrderSIndex
-- ----------------------------
DROP TABLE IF EXISTS "OrderSIndex";
CREATE TABLE "OrderSIndex" (
  "Id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  "OrderName" nvarchar(40),
  "OrderIndexInMonth" integer,
  "CreateDate" datetime,
  "Updated" datetime,
  "ThisMonth" nvarchar(20)
);

PRAGMA foreign_keys = true;

操作方式：

 public string OutIndexStr(string OrderType)
        {
            string YearMonth = $"{DateTime.Now.Year}{DateTime.Now.ToString("MM")}";
            int CountIndex = 0;
            string OutStr;
            string sqlQurey = $"select * from OrderSIndex where OrderName = '{OrderType}' and ThisMonth = '{YearMonth}'";
            string SqlInsert = $"insert into  OrderSIndex (OrderName,OrderIndexInMonth,CreateDate,Updated,ThisMonth) values('{OrderType}',1,datetime('now','localtime'),datetime('now','localtime'),'{YearMonth}')";
            string SqlUpdate = $"Update OrderSIndex set OrderIndexInMonth=OrderIndexInMonth +1 where OrderName = '{OrderType}' and ThisMonth = '{YearMonth}'  ";
            using (IDbConnection Conn = new DALDBBase().GetOpenConn())
            {
                IEnumerable<OrderSIndex> list = Conn.Query<OrderSIndex>(sqlQurey);
                if (list == null||list.Count()==0)
                {
                    Conn.Execute(SqlInsert);
                    OutStr = $"{YearMonth}0001";
                }
                else
                {
                    Conn.Execute(SqlUpdate);
                    OrderSIndex oneModel = list.FirstOrDefault();
                    OutStr = $"{YearMonth}{(oneModel.OrderIndexInMonth+1).ToString().PadLeft(4,'0')}";
                }
            }
            return OutStr;
            
        }

操作类：

  public class OrderSIndex
    {
        public int Id { get; set; }
        public string OrderName { get; set; }
        public int OrderIndexInMonth { get; set; }
        public DateTime CreateDate { get; set; }
        public DateTime Updated { get; set; }
        public string ThisMonth { get; set; }
    }