【代码保留】用于生成RowNumber的程序
因为Sybase没有rownumber，所以最近要做迁移数据很头痛，我的做法是从表A生成一组SQL语句，将rownumber设置成一个特殊的关键字，然后用下面这个程序来将其替换成有效的值。
准确得说，这个代码的功能就是将“字符串A”匹配成一个“前缀+顺序序列”的替换程序。
GenRowNumber.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="GenRowNumber.aspx.cs" Inherits="WebAppClient.GenRowNumber" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>重置Rownumber</title>
</head>
<body>
<form id="form1" runat="server">
<div>
要被替换的字符：<asp:TextBox ID="txtRowNumber" runat="server"></asp:TextBox>（如“row_number”）<br />
生成的字符前缀：<asp:TextBox ID="txtPrefix" runat="server"></asp:TextBox>（如“20091017”，长度必须小于最大长度）<br />
允许的最大长度：<asp:TextBox ID="txtMaxLength" runat="server"></asp:TextBox>（类型：数字）
<br />
每组数据条数：<asp:TextBox ID="txtSQLNumber" runat="server"></asp:TextBox>（如两句SQL一组则填2）<br />
<asp:TextBox ID="txtSQL" runat="server" TextMode="MultiLine" Height="418px" Width="790px"></asp:TextBox>
<br />
<asp:Button ID="btnGenerate" runat="server" Text="Generate" OnClick="btnGenerate_Click" />
<br />
<asp:Label ID="lbOutput" runat="server" Text=""></asp:Label>
<asp:TextBox ID="txtSQLOutput" runat="server" TextMode="MultiLine" Height="418px" Width="790px"></asp:TextBox>
</div>
</form>
</body>
</html>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>重置Rownumber</title>
</head>
<body>
<form id="form1" runat="server">
<div>
要被替换的字符：<asp:TextBox ID="txtRowNumber" runat="server"></asp:TextBox>（如“row_number”）<br />
生成的字符前缀：<asp:TextBox ID="txtPrefix" runat="server"></asp:TextBox>（如“20091017”，长度必须小于最大长度）<br />
允许的最大长度：<asp:TextBox ID="txtMaxLength" runat="server"></asp:TextBox>（类型：数字）
<br />
每组数据条数：<asp:TextBox ID="txtSQLNumber" runat="server"></asp:TextBox>（如两句SQL一组则填2）<br />
<asp:TextBox ID="txtSQL" runat="server" TextMode="MultiLine" Height="418px" Width="790px"></asp:TextBox>
<br />
<asp:Button ID="btnGenerate" runat="server" Text="Generate" OnClick="btnGenerate_Click" />
<br />
<asp:Label ID="lbOutput" runat="server" Text=""></asp:Label>
<asp:TextBox ID="txtSQLOutput" runat="server" TextMode="MultiLine" Height="418px" Width="790px"></asp:TextBox>
</div>
</form>
</body>
</html>
GenRowNumber.aspx.cs
using System;
using System.Data;
using System.Configuration;
using System.Collections;
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;
namespace WebAppClient
{
public partial class GenRowNumber : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnGenerate_Click(object sender, EventArgs e)
{
if (Check())
{
sql = txtSQL.Text;
while (sql.IndexOf(replaceWord) != -1)
{
string currentSeq = GetNewSeqNo();
for (int i = 0; i < sqlNumber; ++i)
{
if (sql.IndexOf(replaceWord) != -1)
{
int firstPos = sql.IndexOf(replaceWord);
int nextPos = firstPos + replaceWord.Length;
sql = sql.Substring(0, firstPos) + currentSeq + sql.Substring(nextPos, sql.Length - nextPos);
}
}
}
sql = sql.Replace("go \"\n", "go\n");
txtSQLOutput.Text = sql;
}
}
private int seq = 0;
private string GetNewSeqNo()
{
++seq;
return prefix + seq.ToString().PadLeft(seqLength, '0');
}
private string sql = string.Empty;
private string replaceWord = string.Empty;
private int maxLength = 0;
private int seqLength = 0;
private string prefix = string.Empty;
private int sqlNumber = 0;
private bool Check()
{
string txtMaxLength = this.txtMaxLength.Text.Trim();
if (!int.TryParse(txtMaxLength, out maxLength))
{
maxLength = 12;
}
prefix = this.txtPrefix.Text.Trim();
if (prefix.Length > maxLength)
{
Output(string.Format("前缀长度（{0}）比最大长度（{1}）还大", prefix.Length.ToString(), maxLength.ToString()));
return false;
}
string txtSqlNumber = this.txtSQLNumber.Text.Trim();
if (!int.TryParse(txtSqlNumber, out sqlNumber))
{
sqlNumber = 1;
}
replaceWord = this.txtRowNumber.Text.Trim();
if (string.IsNullOrEmpty(replaceWord))
{
Output(string.Format("<span style=\"color:red;\">要被替换的字符不能为空</span><br />"));
return false;
}
seqLength = maxLength - prefix.Length;
Output(string.Format("<span style=\"color:red;\">要被替换的字符：{0}</span><br />", replaceWord));
Output(string.Format("<span style=\"color:red;\">生成的字符前缀：{0}</span><br />", prefix));
Output(string.Format("<span style=\"color:red;\">生成的字符前缀长度：{0}</span><br />", prefix.Length.ToString()));
Output(string.Format("<span style=\"color:red;\">允许的最大长度：{0}</span><br />", maxLength.ToString()));
Output(string.Format("<span style=\"color:red;\">每组数据条数：{0}</span><br />", sqlNumber.ToString()));
return true;
}
private void Output(string msg)
{
this.lbOutput.Text += msg + "<br />";
}
}
}
using System.Data;
using System.Configuration;
using System.Collections;
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;
namespace WebAppClient
{
public partial class GenRowNumber : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnGenerate_Click(object sender, EventArgs e)
{
if (Check())
{
sql = txtSQL.Text;
while (sql.IndexOf(replaceWord) != -1)
{
string currentSeq = GetNewSeqNo();
for (int i = 0; i < sqlNumber; ++i)
{
if (sql.IndexOf(replaceWord) != -1)
{
int firstPos = sql.IndexOf(replaceWord);
int nextPos = firstPos + replaceWord.Length;
sql = sql.Substring(0, firstPos) + currentSeq + sql.Substring(nextPos, sql.Length - nextPos);
}
}
}
sql = sql.Replace("go \"\n", "go\n");
txtSQLOutput.Text = sql;
}
}
private int seq = 0;
private string GetNewSeqNo()
{
++seq;
return prefix + seq.ToString().PadLeft(seqLength, '0');
}
private string sql = string.Empty;
private string replaceWord = string.Empty;
private int maxLength = 0;
private int seqLength = 0;
private string prefix = string.Empty;
private int sqlNumber = 0;
private bool Check()
{
string txtMaxLength = this.txtMaxLength.Text.Trim();
if (!int.TryParse(txtMaxLength, out maxLength))
{
maxLength = 12;
}
prefix = this.txtPrefix.Text.Trim();
if (prefix.Length > maxLength)
{
Output(string.Format("前缀长度（{0}）比最大长度（{1}）还大", prefix.Length.ToString(), maxLength.ToString()));
return false;
}
string txtSqlNumber = this.txtSQLNumber.Text.Trim();
if (!int.TryParse(txtSqlNumber, out sqlNumber))
{
sqlNumber = 1;
}
replaceWord = this.txtRowNumber.Text.Trim();
if (string.IsNullOrEmpty(replaceWord))
{
Output(string.Format("<span style=\"color:red;\">要被替换的字符不能为空</span><br />"));
return false;
}
seqLength = maxLength - prefix.Length;
Output(string.Format("<span style=\"color:red;\">要被替换的字符：{0}</span><br />", replaceWord));
Output(string.Format("<span style=\"color:red;\">生成的字符前缀：{0}</span><br />", prefix));
Output(string.Format("<span style=\"color:red;\">生成的字符前缀长度：{0}</span><br />", prefix.Length.ToString()));
Output(string.Format("<span style=\"color:red;\">允许的最大长度：{0}</span><br />", maxLength.ToString()));
Output(string.Format("<span style=\"color:red;\">每组数据条数：{0}</span><br />", sqlNumber.ToString()));
return true;
}
private void Output(string msg)
{
this.lbOutput.Text += msg + "<br />";
}
}
}
posted on 2009-10-20 21:20 volnet（可以叫我大V） 阅读(588) 评论(0) 编辑 收藏 举报
