SQL Server的增删改查
一次关于报警维护界面的书写
采用Fineui+C#+SQLServer完成一个简单的报警维护页面
开发工具 Visual Studio
开发成果
报警维护页面
如图所有功能均可实现
-
输入类别（必须）和备注后点击新增将数据插入数据库并呈现在下表中
若该类别已存在，则提示不要增加 -
输入关键字可进行查询，查询结果在下表中展示
查询页面支持单个字体 -
点击删除，提示是否删除，确定后删除成功
提示是否删除
开发流程
-
首先添加一个新建项
私命名为MachineWarningProgramName 构造前端页面（基于FineUI）
<form id="form1" runat="server">
<f:PageManager ID="PageManager1" AutoSizePanelID="Panel1" runat="server" />
<f:Panel ID="Panel1" runat="server" BodyPadding="5px" ShowBorder="false" Layout="VBox" BoxConfigAlign="Stretch" BoxConfigPosition="Start" ShowHeader="false" Title="報警維護">
<Items>
<f:Form ID="Form" runat="server" ShowHeader="false" ShowBorder="false" LabelWidth="90px">
<Rows>
<f:FormRow>
<Items>
<f:Label ID="Label2" Text="" Width="100" MarginRight="8" runat="server">
</f:Label>
</Items>
</f:FormRow>
</Rows>
</f:Form>
<f:Grid ID="Grid1" runat="server" BoxFlex="1" ShowBorder="true" ShowHeader="false" DataKeyNames="ProgramName,ErrorMessage" EnableRowDoubleClickEvent="true" OnRowCommand="Grid1_RowCommand" OnRowDoubleClick="Grid1_RowDoubleClick">
<Toolbars>
<f:Toolbar ID="Toolbar1" runat="server">
<Items>
<f:TextBox ID="tbProgramName" runat="server" Label="報警類別" LabelWidth="70px" Width="260px" TabIndex="1"></f:TextBox>
<f:TextBox ID="tbErrorMessage" runat="server" Label="信息備註" LabelWidth="70px" Width="260px" TabIndex="1"></f:TextBox>
</Items>
</f:Toolbar>
<f:Toolbar ID="Toolbar2" runat="server">
<Items>
<f:Button ID="btnSave" runat="server" Text="新增" OnClick="btnSave_Click" Icon="Add" ValidateForms="Form" ></f:Button>
<f:Button ID="btnSearch" runat="server" Text="查詢" OnClick="btnSearch_Click" Icon="Zoom" ></f:Button>
<f:HiddenField ID="hidLx" runat="server"></f:HiddenField>
<f:HiddenField ID="HidCb" runat="server"></f:HiddenField>
<f:HiddenField ID="HidQxDm" runat="server"></f:HiddenField>
<f:HiddenField ID="HidDeptId" runat="server"></f:HiddenField>
</Items>
</f:Toolbar>
</Toolbars>
<Columns>
<f:RowNumberField Width="40px" />
<f:BoundField DataField="ProgramName" HeaderText="報警類別" Width="100px" HeaderTextAlign="Center" />
<f:BoundField DataField="ErrorMessage" HeaderText="信息維護" Width="100px" HeaderTextAlign="Center" />
<f:BoundField DataField="C_USER" HeaderText="錄入人" Width="160px" HeaderTextAlign="Center" />
<f:BoundField DataField="C_Date" HeaderText="錄入時間" Width="140px" DataFormatString="{0:yyyy/MM/dd}" HeaderTextAlign="Center" />
<f:BoundField DataField="U_USER" HeaderText="更新人" Width="160px" HeaderTextAlign="Center" />
<f:BoundField DataField="U_Date" HeaderText="更新時間" Width="160px" DataFormatString="{0:yyyy/MM/dd}" HeaderTextAlign="Center" />
<f:LinkButtonField ColumnID="deleteField" TextAlign="Center" Icon="Delete" HeaderText="刪除" ToolTip="刪除" ConfirmText="確定刪除？" ConfirmTarget="Self" CommandName="Delete" Width="100px"></f:LinkButtonField>
</Columns>
</f:Grid>
</Items>
</f:Panel>
</form>
前端构造也就都那么回事，看着长主要是在做格式。
- 功能实现
通过前端各个数据的绑定，进行后端数据的书写。
protected void Page_Load(object sender, EventArgs e)
{
Grid1Bind();
}
private void Grid1Bind()
{
string strDept = " select ProgramName,ErrorMessage,C_user,C_DATE,U_user,U_DATE from ProgramError_ProgramName where ProgramName like '%" + tbProgramName.Text.Trim() + "%' and ErrorMessage like '%" + tbErrorMessage.Text.Trim() + "%'";
DataTable dt = DbHelperSQL.Query(strDept).Tables[0];
Grid1.DataSource = dt;
Grid1.DataBind();
// 查詢programerror_programname表內所有programname數據
}
protected void Grid1_RowCommand(object sender, GridCommandEventArgs e)
{
DataTable dt = Grid1.DataSource as DataTable;
string programname = Grid1.DataKeys[e.RowIndex][0].ToString();
// 選取programname所在行
if (e.CommandName == "Delete")
{
string sql = "delete from ProgramError_ProgramName where programName ='" + dt.Rows[e.RowIndex]["programName"].ToString() + "'";
DbHelperSQL.ExecuteSql(sql);
Grid1Bind();
};
// command事件觸發delete，執行刪除數據
}
protected void btnSearch_Click(object sender, EventArgs e)
{
Grid1Bind();
}
protected void btnSave_Click(object sender, EventArgs e)
{
if (tbProgramName.Text.Trim() == "")
{
Alert.Show("報警類別不能為空!");
return;
}
StringBuilder sb = new StringBuilder();
if (hidLx.Text.Trim() == "")
{
string strSql = "select * from [ProgramError_ProgramName] where ProgramName ='" + tbProgramName.Text.Trim() + "' and [ErrorMessage]='" + tbErrorMessage.Text.Trim().Trim() + " ' ";
DataTable dt = DbHelperSQL.Query(strSql).Tables[0];
if (dt.Rows.Count > 0)
{
Alert.Show("要保存的報警類別已存在，請不要重新增加！");
return;
}
sb.AppendLine(" insert into ProgramError_ProgramName (ProgramName,ErrorMessage,C_user,C_DATE) ");
sb.AppendLine(" values (N'" + tbProgramName.Text.Trim() + "','" + tbErrorMessage.Text.Trim().Trim() + "' ");
sb.AppendLine(" ,'" + GetIdentityName() + "',getdate() )");
}
else
{
sb.AppendLine(" update ProgramError_ProgramName set ProgramName ='" + tbProgramName.Text.Trim() + "',ErrorMessage='" + tbErrorMessage.Text.Trim().Trim() + "' ");
sb.AppendLine(" ,U_USER='" + GetIdentityName() + "',U_DATE=getdate() ");
sb.AppendLine(" where ProgramName ='" + HidCb.Text.Trim() + "' ");
}
try
{
int rows = DbHelperSQL.ExecuteSql(sb.ToString());
if (rows > 0)
{
tbProgramName.Text = "";
tbProgramName.Enabled = true;
tbErrorMessage.Text = "";
hidLx.Text = "";
Grid1Bind();
Alert.Show("添加成功！");
}
}
catch (Exception ex)
{
Alert.Show("添加失敗:" + ex);
return;
}
}
protected void Grid1_RowDoubleClick(object sender, GridRowClickEventArgs e)
{
hidLx.Text = "1";
HidCb.Text = Grid1.DataKeys[e.RowIndex][0].ToString();
tbProgramName.Enabled = false;
tbProgramName.Text = Grid1.DataKeys[e.RowIndex][0].ToString();
tbErrorMessage.Text = Grid1.DataKeys[e.RowIndex][1].ToString();
}
代码真多--
用了将近一星期吸收理解sql server
have a nice day!
