自己做的一道机试题
来源：互联网 发布：盘古网络 编辑：程序博客网 时间：2024/05/23 19:21
两个关联的表的增删查改
主要用了参数 SqlParameter，事务执行多条sql
表Users
ID int
Name varchar
表UsersDetail
ID int
UserId int (对应表Users的ID)
Phone varchar
主要用了参数 SqlParameter，事务执行多条sql
表Users
ID int
Name varchar
表UsersDetail
ID int
UserId int (对应表Users的ID)
Phone varchar
Address varchar
Web.Config
<connectionStrings> <add name="ConnectionString" connectionString="Data Source=local;Initial Catalog=Test;User ID=sa;Password=" providerName="System.Data.SqlClient" /> </connectionStrings>AppCode/DataBase.cs
using System; using System.Collections; using System.Text; using System.Data; using System.Data.SqlClient; using System.Configuration; /// <summary> /// 用于数据访问的类 /// </summary> public class DataBase:IDisposable { protected SqlConnection Connection; protected String ConnectionString; public DataBase() { ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; } ~DataBase() { try { if (Connection != null) Connection.Close(); } catch { } try { Dispose(); } catch { } } protected void Open() { if (Connection == null) { Connection = new SqlConnection(ConnectionString); } if (Connection.State.Equals(ConnectionState.Closed)) { Connection.Open(); } } public void Close() { if (Connection != null) Connection.Close(); } public void Dispose() { if (Connection != null) { Connection.Dispose(); Connection = null; } } public SqlParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size, object Value) { SqlParameter Param; if (Size > 0) Param = new SqlParameter(ParamName, DbType, Size); else Param = new SqlParameter(ParamName, DbType); if (Value != null) Param.Value = Value; return Param; } public DataSet GetDataSet(String SqlString, SqlParameter[] param) { Open(); SqlCommand cmd = new SqlCommand(SqlString, Connection); if (param != null) { cmd.Parameters.AddRange(param); } SqlDataAdapter adapter = new SqlDataAdapter(); adapter.SelectCommand = cmd; DataSet dataset = new DataSet(); adapter.Fill(dataset); Close(); return dataset; } public DataTable GetDataTable(String SqlString, SqlParameter[] param) { DataSet dataset = GetDataSet(SqlString, param); dataset.CaseSensitive = false; return dataset.Tables[0]; } public int ExecuteSQL(string SqlString, SqlParameter[] param) { Open(); try { SqlCommand cmd = new SqlCommand(SqlString, Connection); if (param != null) { cmd.Parameters.AddRange(param); } return cmd.ExecuteNonQuery(); } catch (Exception e) { throw e; } finally { Close(); } } public int ExecuteSQL(String[] SqlStrings, SqlParameter[][] param) { int count = -1; Open(); SqlCommand cmd = new SqlCommand(); SqlTransaction trans = Connection.BeginTransaction(); cmd.Connection = Connection; cmd.Transaction = trans; try { int i = 0; foreach (String str in SqlStrings) { cmd.CommandText = str; cmd.Parameters.AddRange(param[i]); count = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); i++; } trans.Commit(); } catch { trans.Rollback(); count = -1; } finally { Close(); } return count; } public SqlDataReader ExecuteReader(String SqlString, SqlParameter[] param) { Open(); try { SqlCommand cmd = new SqlCommand(SqlString, Connection); if (param != null) { cmd.Parameters.AddRange(param); } SqlDataReader myReader = cmd.ExecuteReader(); return myReader; } catch (Exception e) { throw e; } } public int ExecuteScalar(string SqlString, SqlParameter[] param) { Open(); try { SqlCommand cmd = new SqlCommand(SqlString, Connection); if (param != null) { cmd.Parameters.AddRange(param); } object o = cmd.ExecuteScalar(); return int.Parse(o.ToString()); } catch (Exception e) { throw e; } } } Default3.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default3.aspx.cs" Inherits="Default3" %> <!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>Untitled Page</title> <mce:script type="text/javascript" language="javascript"><!-- function DoCheck() { var inputs = document.getElementById("mytable").getElementsByTagName("input"); for (var i=0; i < inputs.length; i++) if (inputs[i].type == 'checkbox') { inputs[i].checked = document.getElementById("chkall").checked; } } // --></mce:script> <mce:style type="text/css"><!-- #mytable { padding: 0; margin: 0; border-collapse:collapse;} td { border: 1px solid #C1DAD7; background: #fff; font-size:12px; padding: 6px 6px 6px 12px; color: #4f6b72;} td.alt { background: #F5FAFA; color: #797268;} --></mce:style><style type="text/css" mce_bogus="1"> #mytable { padding: 0; margin: 0; border-collapse:collapse;} td { border: 1px solid #C1DAD7; background: #fff; font-size:12px; padding: 6px 6px 6px 12px; color: #4f6b72;} td.alt { background: #F5FAFA; color: #797268;} </style> </head> <body> <form id="form1" runat="server"> 姓名：<asp:TextBox ID="txtName" runat="server"></asp:TextBox> <asp:HiddenField runat="server" ID="hfIDEdit" /> <br /> 电话：<asp:TextBox ID="txtPhone" runat="server"></asp:TextBox><br /> 地址：<asp:TextBox ID="txtAddress" runat="server"></asp:TextBox><br /> <asp:Button ID="btnAdd" runat="server" Text="添加" OnClick="btnAdd_Click" /> <asp:Label ID="lblMsg" runat="server" Text=""></asp:Label><br /> ==================================================================<br /> 输入姓名：<asp:TextBox ID="txtSearchName" runat="server"></asp:TextBox> <asp:Button ID="btnSearch" runat="server" Text="搜索" OnClick="btnSearch_Click" /> <asp:Repeater runat="server" ID="rptUsers" OnItemCommand="rptUsers_ItemCommand" > <HeaderTemplate> <table width="500" id="mytable" cellspacing="0"> <tr> <td class="alt"></td> <td class="alt"> ID</td> <td class="alt">姓名</td> <td class="alt"> 电话</td> <td class="alt">地址</td> <td class="alt"></td> </tr> </HeaderTemplate> <ItemTemplate> <tr> <td> <asp:CheckBox ID="chkDel" runat="server" /> </td> <td> <%#Eval("ID") %> <asp:HiddenField runat="server" ID="hfID" Value='<%#Eval("ID") %>' /> <asp:HiddenField runat="server" ID="hfName" Value='<%#Eval("Name") %>' /> <asp:HiddenField runat="server" ID="hfPhone" Value='<%#Eval("Phone") %>' /> <asp:HiddenField runat="server" ID="hfAddress" Value='<%#Eval("Address") %>' /> </td> <td> <%#Eval("Name") %> </td> <td> <%#Eval("Phone") %> </td> <td> <%#Eval("Address") %> </td> <td> <asp:LinkButton ID="BtnEdit" CommandName="btnEdit" runat="server">编辑</asp:LinkButton> </td> </tr> </ItemTemplate> <FooterTemplate> </table> </FooterTemplate> </asp:Repeater> <input type="checkbox" id="chkall" name="chkall" value="on" onclick="DoCheck();">全选 <asp:Button runat="server" ID="btnDel" Text="删除" OnClick="btnDel_Click" /> </form> </body> </html> Default3.aspx.csusing 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;using System.Data.SqlClient;public partial class Default3 : System.Web.UI.Page{ protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { btnDel.Attributes.Add("onclick", "return confirm('确定进行删除操作吗？');"); BindGV(); } } void BindGV() { DataBase db = new DataBase(); DataSet ds = db.GetDataSet("select * from Users a inner join UsersDetail b on a.ID=b.UserId", null); rptUsers.DataSource = ds; rptUsers.DataBind(); } protected void btnAdd_Click(object sender, EventArgs e) { DataBase db = new DataBase(); if (btnAdd.Text == "添加") { SqlParameter[] Params = new SqlParameter[1]; Params[0] = db.MakeParam("@Name", SqlDbType.VarChar, 20, txtName.Text); string sql = "insert into Users(Name) values(@Name);select @@identity;"; int UserId = db.ExecuteScalar(sql, Params); SqlParameter[] Params2 = new SqlParameter[3]; Params2[0] = db.MakeParam("@UserId", SqlDbType.Int, 4, UserId); Params2[1] = db.MakeParam("@Phone", SqlDbType.VarChar, 20, txtPhone.Text); Params2[2] = db.MakeParam("@Address", SqlDbType.VarChar, 50, txtAddress.Text); string sql2 = "insert into UsersDetail(UserId,Phone,Address) values(@UserId,@Phone,@Address)"; if (db.ExecuteSQL(sql2, Params2) > 0) { lblMsg.Text = "信息添加成功!"; BindGV(); } else { lblMsg.Text = "信息添加失败!"; } } else { SqlParameter[][] Params = new SqlParameter[2][]; SqlParameter[] Params1 = new SqlParameter[2]; Params1[0] = db.MakeParam("@ID", SqlDbType.Int, 4, int.Parse(hfIDEdit.Value)); Params1[1] = db.MakeParam("@Name", SqlDbType.VarChar, 20, txtName.Text); SqlParameter[] Params2 = new SqlParameter[3]; Params2[0] = db.MakeParam("@UserId", SqlDbType.Int, 4, int.Parse(hfIDEdit.Value)); Params2[1] = db.MakeParam("@Phone", SqlDbType.VarChar, 20, txtPhone.Text); Params2[2] = db.MakeParam("@Address", SqlDbType.VarChar, 50, txtAddress.Text); Params[0] = Params1; Params[1] = Params2; string sql1 = "update Users set Name=@Name where ID=@ID"; string sql2 = "update UsersDetail set Phone=@Phone,Address=@Address where UserId=@UserId"; string[] sql = { sql1, sql2 }; if (db.ExecuteSQL(sql, Params) > 0) { lblMsg.Text = "信息修改成功！"; txtName.Text = txtPhone.Text = txtAddress.Text = ""; btnAdd.Text = "添加"; BindGV(); } else { lblMsg.Text = "信息修改失败！"; } } } protected void btnSearch_Click(object sender, EventArgs e) { DataBase db = new DataBase(); SqlParameter[] Params = new SqlParameter[1]; Params[0] = db.MakeParam("@Name", SqlDbType.VarChar, 20, txtSearchName.Text); string sql = "select * from Users a inner join UsersDetail b on a.ID=b.UserId where a.Name=@Name"; DataSet ds = db.GetDataSet(sql, Params); rptUsers.DataSource = ds; rptUsers.DataBind(); } protected void btnDel_Click(object sender, EventArgs e) { DataBase db = new DataBase(); int num = 0; for (int i = 0; i < rptUsers.Items.Count; i++) { HiddenField hfID = (HiddenField)rptUsers.Items[i].FindControl("hfID"); CheckBox chkDel = (CheckBox)rptUsers.Items[i].FindControl("chkDel"); if (chkDel.Checked) { num++; SqlParameter[][] Params = new SqlParameter[2][]; SqlParameter[] Params1 = new SqlParameter[1]; Params1[0] = db.MakeParam("@ID", SqlDbType.Int, 4, int.Parse(hfID.Value)); SqlParameter[] Params2 = new SqlParameter[1]; Params2[0] = db.MakeParam("@UserId", SqlDbType.Int, 4, int.Parse(hfID.Value)); Params[0] = Params1; Params[1] = Params2; string sql1 = "delete from Users where ID=@ID"; string sql2 = "delete from UsersDetail where UserId=@UserId"; string[] sql = { sql1, sql2 }; db.ExecuteSQL(sql, Params); } } if (num > 0) { BindGV(); this.ClientScript.RegisterClientScriptBlock(this.GetType(), "script", "alert('成功删除了！" + num + "条数据');", true); } else { this.ClientScript.RegisterClientScriptBlock(this.GetType(), "script", "alert('没有选择数据！');", true); } } protected void rptUsers_ItemCommand(object source, RepeaterCommandEventArgs e) { HiddenField hfID = (HiddenField)e.Item.FindControl("hfID"); HiddenField hfName = (HiddenField)e.Item.FindControl("hfName"); HiddenField hfPhone = (HiddenField)e.Item.FindControl("hfPhone"); HiddenField hfAddress = (HiddenField)e.Item.FindControl("hfAddress"); switch (e.CommandName) { case "btnEdit": btnAdd.Text = "编辑"; hfIDEdit.Value = hfID.Value; txtName.Text = hfName.Value; txtPhone.Text = hfPhone.Value; txtAddress.Text = hfAddress.Value; break; } }}
- 自己做的一道机试题
- 趋势科技的一道面试题，自己做了一下，就当练练手
- 一道华为的机试题
- 转载的一道小米面试题和自己的写法
- Light OJ 1238--BFS（第一道自己做的）
- 面试的一道机试题： 排序 难度(**)
- 迅雷的一道机试题___URL解析
- 华为的一道机试题，单词倒序
- 一道关于HttpClient的机试题
- 某公司的一道试题
- 一道机试题，拿出来和大家分享一下，稍候贴出我做的代码(C#)
- 闲着做一道内推试题
- 从一道MS试题看自己
- 华为一道机试题
- 一道zhangying机试题
- 一道机试题
- 华为的一道面试题的解答(自己的另解)
- 一道经典的sql面试题!认为自己sql玩得挺转的进来!
- 界面处理相关API&MFC函数（随时更新）
- 批处理导出日期文件名
- AIX 下 DB2 BACKUP
- java日期日间方法
- 装载-常用正则表达式
- 自己做的一道机试题
- mysql_fetch_row,mysql_fetch_array,mysql_fetch_object,mysql_fetch_assoc,的用法及区别
- oracle 实现自动增长
- 苹果之父乔布斯：我是如何东山再起的
- 苹果之父乔布斯：我是如何东山再起的
- not in 查询无记录问题的解决
- .net 2.0 BackgroundWorker类详细用法
- 软件需求管理
- CPU卡知识源代码
