一段自动生成编号的方法,生成编号的方式为当前年份+四位编号,从20110001开始,有跨年。求优化方法。 #region 自动生成变更编号
string sql2 = "SELECT Top 1 Number From LF_Funds Order by Number Desc"; string node2 = "";
if (!this.dbAccessDev.CheckIsExist(sql2))
{
string nYear = System.DateTime.Now.Year.ToString();
string documentNo1 = "0001";
node = nYear + documentNo1; }
else
{
DateTime currentTime = System.DateTime.Now;
int tyear = currentTime.Year;
string ttyear = tyear.ToString();//当前年份
//string tryear = TTyear.Substring(2, 2);
string tmyear = dbAccessDev.QueryValue("SELECT MAX(FillDate) AS Expr1 FROM LF_Funds").ToString();//查找已有记录最新日期 string mmyear = tmyear.Substring(0, 4);//已有记录最新日期的年份 if (String.Compare(ttyear, mmyear) > 0) //判断是否跨年,到第二年则以该年年份为首,重新计数
{
string documentNum = ttyear + "0001";//例如:到09年,则从090001开始计数
node = documentNum; }
else
{
node = this.dbAccessDev.QueryValue(sql2).ToString();
//string[] a = node.Split('-');
//string b = a[1];
int dd = Convert.ToInt32(node);
dd += 1;//流水号加1
node2 = Convert.ToString(dd);
///Node2 = "0"+ Node2;//进行上面的转换时,首位的0没有了,加1后,在首位加“0”
node = node2; }
}
return node;
#endregion
string sql2 = "SELECT Top 1 Number From LF_Funds Order by Number Desc"; string node2 = "";
if (!this.dbAccessDev.CheckIsExist(sql2))
{
string nYear = System.DateTime.Now.Year.ToString();
string documentNo1 = "0001";
node = nYear + documentNo1; }
else
{
DateTime currentTime = System.DateTime.Now;
int tyear = currentTime.Year;
string ttyear = tyear.ToString();//当前年份
//string tryear = TTyear.Substring(2, 2);
string tmyear = dbAccessDev.QueryValue("SELECT MAX(FillDate) AS Expr1 FROM LF_Funds").ToString();//查找已有记录最新日期 string mmyear = tmyear.Substring(0, 4);//已有记录最新日期的年份 if (String.Compare(ttyear, mmyear) > 0) //判断是否跨年,到第二年则以该年年份为首,重新计数
{
string documentNum = ttyear + "0001";//例如:到09年,则从090001开始计数
node = documentNum; }
else
{
node = this.dbAccessDev.QueryValue(sql2).ToString();
//string[] a = node.Split('-');
//string b = a[1];
int dd = Convert.ToInt32(node);
dd += 1;//流水号加1
node2 = Convert.ToString(dd);
///Node2 = "0"+ Node2;//进行上面的转换时,首位的0没有了,加1后,在首位加“0”
node = node2; }
}
return node;
#endregion
Select right('0000'+Cast(isnull(MAX(ID),0)+1 as varchar(4)),4) from 表
where year=year
var Code=string.Formate("{0:yyyy}{1:0000}",DateTime.Now,Number);
string Year = DateTime.Now.Year.ToString();
string Sql = "SELECT Number,RIGHT(Number,5) AS NewNumber From LF_Funds WHERE LEFT(Number,4) = year(getdate()) Order by Number Desc";
DataTable dt = dbAccessDev.QueryDataTable(Sql);
if(dt.rows.Count == 0)
{
No = Year + "0001";
}
else
{
No = Year + (Convert.ToInt32(dt.rows[0]["NewNumber"]) + 1).ToString();
}
return No;