数据的增删改
<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;
}