shawl.qiu c# .net 自动生成 OleDb 数据操作语句小工具 Producing OleDbSql 1.1

最新推荐文章于 2024-10-18 17:23:02 发布

btbtd

最新推荐文章于 2024-10-18 17:23:02 发布

阅读量1.7k

点赞数

文章标签： .net c# 工具 textbox server asp

本文链接：https://blog.csdn.net/btbtd/article/details/1597523

版权

MS C#.Net 专栏收录该内容

16 篇文章 0 订阅

订阅专栏

shawl.qiu c# .net 自动生成 OleDb 数据操作语句小工具 Producing OleDbSql 1.1

说明:
这个小工具主要用途就是, 我希望输入表字段, 如: gbname, gbeditpwd, gbcompanyname, gbtel, gbmobile, gbemail, gbfax, gbqq, gbmsn, gbhp, gbsubject, gbtext, gbubbdisable, gbnopublic, 每个表字段的字段名都对应着某某控件.

当我按提交后, 自动生成所需数据操作语句,,,
目前写.NET主要使用 OleDbConnection, OleDbCommand + OleDbParameter
至于为什么选择 OleDbParameter, 当然就是单引号问题了...

目前只生成针对 Access 的操作语句, 因这阵子没需求去使用 SqlServer, 就懒得弄了...

下载:
http://files.myopera.com/btbtd/csharp/Tools/Producing_OleDbSql.7z

目录:
1. 生成格式预览
2. Producing_OleDbSql.aspx

shawl.qiu
2007-05-05
http://blog.csdn.net/btbtd

内容:
1. 生成格式预览

OleDbConnection oCnn = new OleDbConnection(Sys.Conn);
OleDbCommand oCmd = new OleDbCommand(
"insert into guestbook"+
"(gbname,gbeditpwd,gbcompanyname,gbtel,gbmobile,gbemail,gbfax,gbqq,gbmsn,gbhp,gbsubject,gbtext,gbubbdisable,gbnopublic) "+
" values"+
"(@gbname,@gbeditpwd,@gbcompanyname,@gbtel,@gbmobile,@gbemail,@gbfax,@gbqq,@gbmsn,@gbhp,@gbsubject,@gbtext,@gbubbdisable,@gbnopublic)",
oCnn);
OleDbParameter Param1 = new OleDbParameter("@gbname", "OleDbType.Varchar");
Param1.Value = gbname.Text;
oCmd.Parameters.Add(Param1);
OleDbParameter Param2 = new OleDbParameter("@gbeditpwd", "OleDbType.Varchar");
Param2.Value = gbeditpwd.Text;
oCmd.Parameters.Add(Param2);
OleDbParameter Param3 = new OleDbParameter("@gbcompanyname", "OleDbType.Varchar");
Param3.Value = gbcompanyname.Text;
oCmd.Parameters.Add(Param3);
OleDbParameter Param4 = new OleDbParameter("@gbtel", "OleDbType.Varchar");
Param4.Value = gbtel.Text;
oCmd.Parameters.Add(Param4);
OleDbParameter Param5 = new OleDbParameter("@gbmobile", "OleDbType.Varchar");
Param5.Value = gbmobile.Text;
oCmd.Parameters.Add(Param5);
OleDbParameter Param6 = new OleDbParameter("@gbemail", "OleDbType.Varchar");
Param6.Value = gbemail.Text;
oCmd.Parameters.Add(Param6);
OleDbParameter Param7 = new OleDbParameter("@gbfax", "OleDbType.Varchar");
Param7.Value = gbfax.Text;
oCmd.Parameters.Add(Param7);
OleDbParameter Param8 = new OleDbParameter("@gbqq", "OleDbType.Varchar");
Param8.Value = gbqq.Text;
oCmd.Parameters.Add(Param8);
OleDbParameter Param9 = new OleDbParameter("@gbmsn", "OleDbType.Varchar");
Param9.Value = gbmsn.Text;
oCmd.Parameters.Add(Param9);
OleDbParameter Param10 = new OleDbParameter("@gbhp", "OleDbType.Varchar");
Param10.Value = gbhp.Text;
oCmd.Parameters.Add(Param10);
OleDbParameter Param11 = new OleDbParameter("@gbsubject", "OleDbType.Varchar");
Param11.Value = gbsubject.Text;
oCmd.Parameters.Add(Param11);
OleDbParameter Param12 = new OleDbParameter("@gbtext", "OleDbType.Varchar");
Param12.Value = gbtext.Text;
oCmd.Parameters.Add(Param12);
OleDbParameter Param13 = new OleDbParameter("@gbubbdisable", "OleDbType.Varchar");
Param13.Value = gbubbdisable.Checked?"1":"0";
oCmd.Parameters.Add(Param13);
OleDbParameter Param14 = new OleDbParameter("@gbnopublic", "OleDbType.Varchar");
Param14.Value = gbnopublic.Checked?"1":"0";
oCmd.Parameters.Add(Param14);
oCnn.Open();
oCmd.ExecuteNonQuery();
oCnn.Close();
Utility.GoBack("操作完毕，3 秒后返回，还有 ", 3, true, InfoLbl);

2. Producing_OleDbSql.aspx

<%@ Page Language="C#" AutoEventWireup="True" %>
<script runat="server">
string AuSubject = "shawl.qiu .net Tools - Producing OleDbSql";
string AuVersion = "1.1";
void Page_Load(Object s, EventArgs e)
{
} // end Page_Load
public static void ClearSpace(ref string ipt)
{
ipt = Regex.Replace(ipt, @"/s+", "");
}
void SubmitFunc(Object s, EventArgs e)
{
bool Debug = false;
if(Debug)
{
Response.Write("<li/>SubmitFunc: Ok");
}
string sCnnName = Cnn.Text;
string sCmdName = Cmd.Text;
string sTblName = TblName.Text;
string sCnnStrName = ConnStr.Text;
string sParamPrefix = Param.Text;
string sSet = Columns.Text;
ClearSpace(ref sSet);
string[] sAr = sSet.Split(',');
Response.Write("<xmp>");
Response.Write("OleDbConnection "+sCnnName+" = new OleDbConnection("+sCnnStrName+");/r/n");
Response.Write("OleDbCommand "+sCmdName+" = new OleDbCommand(/r/n");
if(opt.SelectedValue == "insert")
{
Response.Write(" /"insert into "+sTblName+"/"+/r/n");
Response.Write(" /"("+sSet+") /"+/r/n");
Response.Write(" /" values/"+/r/n");
Response.Write(" /"("+Regex.Replace(sSet, "([a-zA-Z0-9-_]+)", "@$1")+")/"");
}
else
{
Response.Write("/"update "+sTblName+" set /"+/r/n");
Response.Write("/""+Regex.Replace(sSet, "([a-zA-Z0-9-_]+)", "$1=@$1"));
Response.Write(" where ");
Response.Write(UpdateCdti.Text);
}
Response.Write(",/r/n "+sCnnName+");/r/n/r/n");
for(int i=0; i<sAr.Length; i++)
{
Response.Write("OleDbParameter "+sParamPrefix+(i+1)+" = new OleDbParameter(/"@"+sAr[i]+
"/", /"OleDbType.Varchar/");"+"/r/n");
if(IsCheckBox.Text!="")
{
if(Test(IsCheckBox.Text, @"/b"+sAr[i]+@"/b"))
{
Response.Write(sParamPrefix+(i+1)+".Value = "+sAr[i]+".Checked?/"1/":/"0/";/r/n");
}
else
{
Response.Write(sParamPrefix+(i+1)+".Value = "+sAr[i]+".Text;/r/n");
}
}
else
{
Response.Write(sParamPrefix+(i+1)+".Value = "+sAr[i]+".Text;/r/n");
}
Response.Write(sCmdName+".Parameters.Add("+sParamPrefix+(i+1)+"); /r/n/r/n");
}
Response.Write(sCnnName+".Open();/r/n");
Response.Write(sCmdName+".ExecuteNonQuery();/r/n");
Response.Write(sCnnName+".Close();/r/n/r/n");
Response.Write("Utility.GoBack(/""+GoBackMsg.Text+" /", "+GoBackSecond.Text+
", "+GoBackUrl.Text+", "+GoBackLbl.Text+");/r/n/r/n");
Response.Write("</xmp>");
} // end void SubmitFunc
public static bool Test(string ipt, string ptr, RegexOptions ro)
{
return Regex.IsMatch(@ipt, @ptr, ro);
}
public static bool Test(string ipt, string ptr)
{
return Regex.IsMatch(@ipt, @ptr, RegexOptions.IgnoreCase);
}
</script>
<!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>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>shawl.qiu template</title>
<style type="text/css">
/* <![CDATA[ */
/* shawl.qiu CSS Document : shawl.qiu@gmail.com */
/* shawl.qiu CSS 通用设置 2.1 2007-5-4 16:28:11 */
/* begin general css definition */
html, body
{/* 设置滚动条颜色 - IE */
scrollbar-arrow-color: #ffffff; /*箭头*/
scrollbar-track-color: #ffffff; /*底色*/
scrollbar-base-color: #79F479; /*基本色调*/
scrollbar-face-color: #03D100; /*表面*/
scrollbar-dark-shadow-color:#252523; /*阴影*/
}
body
{
margin:0px auto;
width:760px;
background-color:#fff;
font: 14px Arial, Helvetica, sans-serif, 宋体;
}
*
{
margin:0px;
padding:0px;
text-decoration:none;
}
* img
{
border:0px;
}
.corDc{ background-color:rgb(248,248,248); }
.corLc{ background-color:rgb(252,252,252); }
.corRed {color:red;}
.corGreen {color:green;}
.corBlack {color:black;}
.corYellow{color:yellow;}
.corWhite{color:white;}
.corFuchsia{color:fuchsia;}
.corWhiteAll, .corWhiteAll *, .corWhiteAll a:visited
{
color:white;
}
.hl
{
background-color:yellow;
color:black;
margin:5px 0px;
padding:2px;
text-align:center;
}
.hlLc
{
background-color:#F2F2F2;
color:black;
margin:5px 0px;
padding:2px;
text-align:center;
}
.algr{ text-align:right;}
.algl{ text-align:left; }
.algc{ text-align:center;}
.dspIl{ display:inline;}
.dspNn{ display:none;}
.fltr{ float:right; }
.fltl{ float:left; }
.ftBold{font-weight:bold}
a:link{ color:black;}
a:hover{ background-color:transparent; color:black;}
a:visited{ background-color:transparent;}
.cboth{ clear:both; }
hr
{
border-top: 1px dashed #FFFFFF!important;
border-right: 1px dashed #FFFFFF!important;
border-bottom:1px dashed black!important;
border-left: 1px dashed #FFFFFF!important;
margin:10px 0px;
}
p{ margin:5px 0px; }
.sqPagedCurLink
{
color: #FFCCFF;
font-weight:bold;
}
.sqPagedNonLink
{
color:#ccc;
}
.sqPagedJumpBox
{
border:0px;
border-bottom:1px dashed black;
width:48px;
}
.TblSolidBorder
{
background-color:#FFFFFF !important; /*表格背景色绝对白色*/
border-collapse: collapse; /*清除 IE 的默认边框*/
border: 1px solid #999999;
}
.TblSolidBorder tr td
{
padding:2px!important;
}
.TblSolidBorder td, .TblSolidBorder th
{
border-top: 1px solid #999999; /* 设置 1px 比设置为 thin 通用 */
border-right: 1px solid #999999;
border-collapse: collapse; /*清除 IE 默认边框*/
padding:2px;
}
/* end general css definition */
/* start for dot net definition */
.MainDl
{
}
.MainDl table
{
margin:5px 0px;
}
.MainDl tr td table tr td
{
padding:2px;
}
.MainTbl
{
margin:5px 0px;
}
.MainTbl tr td
{
padding:2px;
}
.MainTblTitle
{
background-color:#F2F2F2;
text-align:center;
padding:5px 0px;
}
/* end for dot net definition */
/* ]]> */
</style>
</head>
<body>
<form runat="server">
<table width="90%" border="0" align="center" cellpadding="0" cellspacing="0" class="MainTbl">
<tr>
<td colspan="2" align="center">
<div class="hlLc">
<% Response.Write(AuSubject); %> <% Response.Write(AuVersion); %>
</div>
</td>
</tr>
<tr>
<td align="right">Option: </td>
<td>
<asp:DropDownList ID="opt" runat="server">
<asp:ListItem Value="insert" Selected="true">insert</asp:ListItem>
<asp:ListItem Value="update">update</asp:ListItem>
</asp:DropDownList>
</td>
</tr>
<tr>
<td align="right">Columns String:</td>
<td>
<asp:TextBox ID="Columns" runat="server"
Columns="60"
Text="gbname, gbeditpwd, gbcompanyname, gbtel, gbmobile, gbemail, gbfax, gbqq, gbmsn, gbhp, gbsubject, gbtext, gbubbdisable, gbnopublic"
/>(column,column,column)
</td>
</tr>
<tr>
<td align="right">Update Condition: </td>
<td>
<asp:TextBox ID="UpdateCdti" runat="server"
Text="id="
/><font color="red">注意!如果是更新语句, 请记得指定条件!(如: id="+variable)</font>
</td>
</tr>
<tr>
<td width="180" align="right">OleDbConnection Name: </td>
<td>
<asp:TextBox ID="Cnn" runat="server"
Text="oCnn"
/>
</td>
</tr>
<tr>
<td width="180" align="right">OleDbCommand Name: </td>
<td>
<asp:TextBox ID="Cmd" runat="server"
Text="oCmd"
/>
</td>
</tr>
<tr>
<td width="180" align="right">Table Name: </td>
<td>
<asp:TextBox ID="TblName" runat="server"
Text="guestbook"
/>
</td>
</tr>
<tr>
<td width="180" align="right">Connection String: </td>
<td>
<asp:TextBox ID="ConnStr" runat="server"
Columns="60"
Text="Sys.Conn"
/>
</td>
</tr>
<tr>
<td width="180" align="right">OleDbParameter Prefix: </td>
<td>
<asp:TextBox ID="Param" runat="server"
Text="Param"
/>
</td>
</tr>
<tr>
<td width="180" align="right">GoBack Message: </td>
<td>
<asp:TextBox ID="GoBackMsg" runat="server"
Text="操作完毕，3 秒后返回，还有"
Columns="60"
/>
</td>
</tr>
<tr>
<td align="right">GoBack Second:: </td>
<td>
<asp:TextBox ID="GoBackSecond" runat="server"
Text="3"
/>
</td>
</tr>
<tr>
<td align="right">GoBack Url: </td>
<td>
<asp:TextBox ID="GoBackUrl" runat="server"
Text="true"
Columns="60"
/>
</td>
</tr>
<tr>
<td align="right">GoBack Label: </td>
<td>
<asp:TextBox ID="GoBackLbl" runat="server"
Text="InfoLbl"
/>
</td>
</tr>
<tr>
<td align="right"> </td>
<td> </td>
</tr>
<tr>
<td align="right">Is CheckBox:</td>
<td>
<asp:TextBox ID="IsCheckBox" runat="server"
Text="gbubbdisable, gbnopublic"
/>
</td>
</tr>
<tr>
<td width="180" align="right"> </td>
<td>
<input type="submit" runat="server"
value="提交"
οnclick="return confirm('现在提交更改吗?')"
OnServerClick="SubmitFunc"
/>
<input type="reset" runat="server"
value="重置"
οnclick="return confirm('现在重置吗?')"
/>
</td>
</tr>
</table>
<p> </p>
<table width="90%" border="0" align="right" cellpadding="0" cellspacing="0" class="TblSolidBorder">
<tr>
<td width="120" align="right" valign="top">Subject: </td>
<td align="left" valign="top"><% Response.Write(AuSubject); %></td>
</tr>
<tr>
<td width="120" align="right" valign="top">Version:</td>
<td align="left" valign="top"><% Response.Write(AuVersion); %></td>
</tr>
<tr>
<td width="120" align="right" valign="top">Author:</td>
<td align="left" valign="top">shawl.qiu</td>
</tr>
<tr>
<td width="120" align="right" valign="top">E-Mail:</td>
<td align="left" valign="top">shawl.qiu@gmail.com</td>
</tr>
<tr>
<td width="120" align="right" valign="top">Blog:</td>
<td align="left" valign="top">http://blog.csdn.net/btbtd</td>
</tr>
<tr>
<td align="right" valign="top">Created Date: </td>
<td align="left" valign="top">2007-5-4</td>
</tr>
</table>
<p> </p>
</form>
</body>
</html>