数据的增删改

最新推荐文章于 2021-03-15 22:41:50 发布

转载最新推荐文章于 2021-03-15 22:41:50 发布 · 149 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/tingtang/archive/2008/04/17/1157677.html

文章标签：

#数据库 #ui

1.config增加ConnectionString
<configuration>
    <appSettings>
     <add key="ConnectionString" value="server=127.0.0.1;database=Northwind;uid=sa;pwd=" />
    </appSettings>
</configuration>

2.增加一条记录
private string type
  {
   get
   {
    return ViewState["type"]==null?"":ViewState["type"].ToString();
   }
   set
   {
    ViewState["type"] = value;
   }
  }

if(!this.IsPostBack)
   {
    type = Request.Params["type"].ToString();
   }

private void Button1_Click(object sender, System.EventArgs e)
  {
   int id = getMaxTypeID();
   id++;
   string name = this.TextBox1.Text.Trim();
   string mark = this.TextBox2.Text.Trim();

   SqlConnection conn = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
   string sql = "insert into roles(id,typename,rolename,remark)values("+id+",'"+type+"','"+ name +"','"+mark+"') ";
   SqlCommand cmd = new SqlCommand(sql,conn);

   try
   {
    conn.Open();
    cmd.ExecuteNonQuery();
   }
   catch(System.Data.SqlClient.SqlException E)
   {
    conn.Close();
    throw new Exception(E.Message);
   }
  }

  private int getMaxTypeID()
  {
   SqlConnection conn = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
   string sql = "select max(id) from roles ";
   SqlCommand cmd = new SqlCommand(sql,conn);

   try
   {
    conn.Open();
    return cmd.ExecuteScalar()==null?0:int.Parse(cmd.ExecuteScalar().ToString());
   }
   catch(System.Data.SqlClient.SqlException E)
   {
    conn.Close();
    throw new Exception(E.Message);
   }
  }

3.显示数据
<asp:datagrid id="grid" runat="server" AllowPaging="True" AutoGenerateColumns="False" Width="100%"
        DataKeyField="ID" CellPadding="4" BackColor="White" BorderWidth="1px" BorderStyle="None" BorderColor="#3366CC">
        <FooterStyle ForeColor="#003399" BackColor="#99CCCC"></FooterStyle>
        <SelectedItemStyle Font-Bold="True" ForeColor="#CCFF99" BackColor="#009999"></SelectedItemStyle>
        <ItemStyle ForeColor="#003399" BackColor="White"></ItemStyle>
        <HeaderStyle Font-Bold="True" ForeColor="#CCCCFF" BackColor="#003399"></HeaderStyle>
        <Columns>
         <asp:BoundColumn DataField="ID" ReadOnly="True" HeaderText="编号">
          <HeaderStyle Width="60px"></HeaderStyle>
         </asp:BoundColumn>
         <asp:BoundColumn DataField="typename" HeaderText="角色类型">
          <HeaderStyle Width="120px"></HeaderStyle>
         </asp:BoundColumn>
         <asp:BoundColumn DataField="rolename" ReadOnly="True" HeaderText="角色名称">
          <HeaderStyle Width="120px"></HeaderStyle>
         </asp:BoundColumn>
         <asp:BoundColumn DataField="remark" ReadOnly="True" HeaderText="角色描述"></asp:BoundColumn>
         <asp:BoundColumn ReadOnly="True" HeaderText="ID"></asp:BoundColumn>
         <asp:HyperLinkColumn Text="详细" DataNavigateUrlFormatString="Show.aspx?id={0}" HeaderText="详细">
          <HeaderStyle Width="60px"></HeaderStyle>
         </asp:HyperLinkColumn>
         <asp:HyperLinkColumn Text="修改" DataNavigateUrlFormatString="Modify.aspx?id={0}" HeaderText="修改">
          <HeaderStyle Width="60px"></HeaderStyle>
         </asp:HyperLinkColumn>
         <asp:HyperLinkColumn Text="删除" DataNavigateUrlFormatString="Delete.aspx?id={0}" HeaderText="删除">
          <HeaderStyle Width="60px"></HeaderStyle>
         </asp:HyperLinkColumn>
        </Columns>
        <PagerStyle Visible="False" HorizontalAlign="Left" ForeColor="#003399" BackColor="#99CCCC" Mode="NumericPages"></PagerStyle>
       </asp:datagrid>

private void Bind()
  {
   SqlConnection conn = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
   string sql = "select * from roles ";

   DataSet ds = new DataSet();
   try
   {
    conn.Open();
    SqlDataAdapter command = new SqlDataAdapter(sql,conn);
    command.Fill(ds,"ds");
   }
   catch(System.Data.SqlClient.SqlException E)
   {
    conn.Close();
    throw new Exception(E.Message);
   }

   this.grid.DataSource = ds;
   this.grid.DataBind();
  }

  private void grid_ItemDataBound(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e)
  {
   foreach(System.Web.UI.WebControls.HyperLink link in e.Item.Cells[7].Controls)
   {
    link.Attributes.Add("onClick","if (!window.confirm('您真的要删除这条记录吗？')){return false;}");
   }
   if(e.Item.ItemIndex>=0)
   {
    e.Item.Attributes.Add("onMouseOn","this.style.backgroundColor='green'; this.style.cursor='hand';");
    e.Item.Attributes.Add("onMouseOut","this.style.backgroundColor='white';");
   }
  }

4.取一条记录
DataSet ds = new DataSet();
    try
    {
     connection.Open();
     SqlDataAdapter command = new SqlDataAdapter(SQLString,connection);
     command.Fill(ds,"ds");
    }
    catch(System.Data.SqlClient.SqlException ex)
    {
     throw new Exception(ex.Message);
    }

if(ds.Tables[0].Rows.Count>0)
   {
    if(ds.Tables[0].Rows[0]["FatherID"].ToString()!="")
    {
     model.FatherID=int.Parse(ds.Tables[0].Rows[0]["FatherID"].ToString());
    }
    model.Name=ds.Tables[0].Rows[0]["Name"].ToString();
    if(ds.Tables[0].Rows[0]["TaxisID"].ToString()!="")
    {
     model.TaxisID=int.Parse(ds.Tables[0].Rows[0]["TaxisID"].ToString());
    }
    if(ds.Tables[0].Rows[0]["AddTime"].ToString()!="")
    {
     model.AddTime=DateTime.Parse(ds.Tables[0].Rows[0]["AddTime"].ToString());
    }
    model.FontColor=ds.Tables[0].Rows[0]["FontColor"].ToString();
    model.IfStop=ds.Tables[0].Rows[0]["IfStop"].ToString();
    model.IfDel=ds.Tables[0].Rows[0]["IfDel"].ToString();
    model.Remark=ds.Tables[0].Rows[0]["Remark"].ToString();
    model.IfIndex=ds.Tables[0].Rows[0]["IfIndex"].ToString();
    return model;
   }
   else
   {
    return null;
   }