建一个名称为ClassBillNumber的类 代码如下：
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
// 添加命名空间
using System.Data.SqlClient;
///
/// ClassBillNumber 的摘要说明
///
public class ClassBillNumber
{
SqlConnection sqlcon;
SqlCommand
sqlcmd;
string
strCon = "Data Source=(local);Database=student;Uid=sa;Pwd=123";
public
ClassBillNumber()
{
}
public
string MakeBillNumber(string sSheetType)//
参数sSheetType为流水号前面的前缀
{
int number1 = 0, number2 = 0;
string Year = "", Month = "", Day = "Day";
Year = DateTime.Now.ToString("yyyy");
Month =DateTime.Now.Month.ToString();//获得当前月
Year = DateTime.Now.Year.ToString();//获得当前年
Day = Day + DateTime.Now.Day.ToString();//获得当前日
string sqlstr = string.Empty;
// 判断数据库里是否有当前年、月的数据，如果有进行查询，如果没有进行插入数据操作
sqlstr = sqlstr + " if Not Exists(select * from systemdb where
(SheetType='" +
sSheetType + "') " + " and (NYear='" + Year +
" ')"+" and (NMonth='"+Month+"'))";
sqlstr = sqlstr + " Insert SystemDB(SheetType,NYear,NMonth)
Values('" + sSheetType +
" ','" + Year + " ','"+Month +"')";
sqlstr = sqlstr + "Select * From SystemDB Where (SheetType='" +
sSheetType + "') And
(NYear='" + Year + "') And (NMonth='" + Month + "')";
sqlcon = new SqlConnection(strCon);
SqlDataAdapter da = new SqlDataAdapter(sqlstr, sqlcon);
DataSet ds = new DataSet();
sqlcon.Open();
da.Fill(ds, "lcb");
if (ds.Tables[0].Rows[0][Day].ToString() == "")
{
number1 = 0;// 如果是新插入的数据，当前基数为0
}
else
{
// 如果数据已经存在，读出基数
number1 =
Convert.ToInt32(ds.Tables[0].Rows[0][Day].ToString());
}
if (number1 >= 9999)
{
number2 = 1;
}
else
{
if (number1 <= 0000)
{
number1 = 1;
}
number2 = number1 + 1;// 计算新的基数
}
string MaxCode = String.Format("{0:D4}", number1);
// 每执行一次增加一次数据库里当前日的基数
sqlstr = "update systemdb set " + Day + "=" + number2 + " where
sheettype='" +
sSheetType + "' and nyear='" + Year + " ' and
NMonth='" + Month + "'";
sqlcon = new SqlConnection(strCon);
sqlcon.Open();
sqlcmd = new SqlCommand(sqlstr, sqlcon);
int i = sqlcmd.ExecuteNonQuery();
return sSheetType + DateTime.Now.ToString("yyyyMMdd") +
MaxCode;
}
}
程序的Page_Load事件代码：
protected void Page_Load(object sender, EventArgs
e)
{
ClassBillNumber aa = new ClassBillNumber();
TextBox1.Text = aa.MakeBillNumber("J");
}
数据库脚本：
CREATE TABLE [dbo].[SystemDB] (
[SheetType] [varchar] (2) COLLATE
Chinese_PRC_CI_AS NULL ,
[NYear] [smallint] NOT NULL ,
[NMonth] [int] NOT NULL ,
[Day1] [int] NULL ,
[Day2] [int] NULL ,
[Day3] [int] NULL ,
[Day4] [int] NULL ,
[Day5] [int] NULL ,
[Day6] [int] NULL ,
[Day7] [int] NULL ,
[Day8] [int] NULL ,
[Day9] [int] NULL ,
[Day10] [int] NULL ,
[Day11] [int] NULL ,
[Day12] [int] NULL ,
[Day13] [int] NULL ,
[Day14] [int] NULL ,
[Day15] [int] NULL ,
[Day16] [int] NULL ,
[Day17] [int] NULL ,
[Day18] [int] NULL ,
[Day19] [int] NULL ,
[Day20] [int] NULL ,
[Day21] [int] NULL ,
[Day22] [int] NULL ,
[Day23] [int] NULL ,
[Day24] [int] NULL ,
[Day25] [int] NULL ,
[Day26] [int] NULL ,
[Day27] [int] NULL ,
[Day28] [int] NULL ,
[Day29] [int] NULL ,
[Day30] [int] NULL ,
[Day31] [int] NULL
) ON [PRIMARY]
GO
转播
