数据交换学习二：asp.net C# 对 sqlserver表的操作

using System;

using System.Collections;

using System.Configuration;

using System.Data;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Data.SqlClient;

 

//显示数据示例 

public partial class Default2 : System.Web.UI.Page

{

    protected void Page_Load(object sender, EventArgs e)

    {

        //创建表及插入数据

        //use jwcrm

        //create table userinfo(username char(10),userpwd char(10))

        //insert into userInfo values('潘万飞','123456')

        //insert into userInfo values('李世明','678910')

        //select * from userinfo

       

        //连接SQL

        SqlConnection thisConnection = new SqlConnection("Server=127.0.0.1;uid=sa;pwd=123;database=jwcrm");

 

        //创建数据库适配器

        SqlDataAdapter thisAdapter = new SqlDataAdapter("select * from userinfo", thisConnection);

        SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter);

        DataSet thisDataSet = new DataSet();

        thisAdapter.Fill(thisDataSet, "userinfo");

        int i = 0;

        foreach(DataRow rowData in thisDataSet.Tables[0].Rows)

        {

            i++;

            Response.Write("序　号：" + i + "<br>");

            Response.Write("用户名：" + rowData["userName"] + "<br>");

            Response.Write("密　码：" + rowData["userpwd"]  + "<br>");

        }

    }

}

 

 

 

//增加数据行

public partial class Default3 : System.Web.UI.Page

{

    protected void Page_Load(object sender, EventArgs e)

    {

        SqlConnection thisConnection = new SqlConnection("Server=127.0.0.1;uid=sa;pwd=123;database=jwcrm");

 

        //创建数据库适配器

        SqlDataAdapter thisAdapter = new SqlDataAdapter("select * from userinfo", thisConnection);

        SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter);

        DataSet thisDataSet = new DataSet();

        thisAdapter.Fill(thisDataSet, "userinfo");

        Response.Write(thisDataSet.Tables["userinfo"].Rows.Count);

        DataRow thisRow = thisDataSet.Tables["userinfo"].NewRow();

        thisRow["username"] = "安娇娇";

        thisRow["userpwd"]  = "663333";

        thisDataSet.Tables["userinfo"].Rows.Add(thisRow);

        thisAdapter.Update(thisDataSet, "userinfo");

        thisConnection.Close();

    }

}

 

 

//简单数据更新示例

public partial class update_sample : System.Web.UI.Page

{

    protected void Page_Load(object sender, EventArgs e)

    {

        //创建表及插入数据

        //use jwcrm

        //create userinfo(username char(10) primary key,userpwd char(10))

        //insert into userInfo values('潘万飞','123456')

        //insert into userInfo values('李世明','678910')

        //select * from userinfo

 

        //连接SQL

        SqlConnection thisConnection = new SqlConnection("Server=127.0.0.1;uid=sa;pwd=123;database=jwcrm");

 

        //创建数据库适配器

        SqlDataAdapter thisAdapter = new SqlDataAdapter("select * from userinfo", thisConnection);

        SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter);

        DataSet thisDataSet = new DataSet();

        thisAdapter.Fill(thisDataSet, "userinfo");

        //更新数据 表中要有primary key哦，不然不能更新的

        thisDataSet.Tables["userinfo"].Rows[1]["userpwd"] = "00000";

        thisAdapter.Update(thisDataSet, "userinfo");

    }

}

 

 

 

//查询记录是否存在

public partial class finddata_sample : System.Web.UI.Page

{

    protected void Page_Load(object sender, EventArgs e)

    {

        SqlConnection thisConnection = new SqlConnection("Server=127.0.0.1;uid=sa;pwd=123;database=jwcrm");

        //创建数据库适配器

        SqlDataAdapter thisAdapter = new SqlDataAdapter("select * from userinfo", thisConnection);

        SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter);

        DataSet thisDataSet = new DataSet();

        thisAdapter.Fill(thisDataSet, "userinfo");

        //查询部分代码

        DataColumn[] keys = new DataColumn[1];

        keys[0] = thisDataSet.Tables["userinfo"].Columns["username"];

        thisDataSet.Tables["userinfo"].PrimaryKey = keys;

        string cUserName = "潘万飞";

        DataRow findRow = thisDataSet.Tables["userinfo"].Rows.Find(cUserName);

        if (findRow == null)

        {

            Response.Write("未找到“" + cUserName + "”!");

        }

        else

        {

            Response.Write("已经找到“" + cUserName + "”!");

        }

    }

} 

 

//查询记录是否存在并对其删除

public partial class finddata_sample : System.Web.UI.Page

{

    protected void Page_Load(object sender, EventArgs e)

    {

        SqlConnection thisConnection = new SqlConnection("Server=127.0.0.1;uid=sa;pwd=123;database=jwcrm");

        //创建数据库适配器

        SqlDataAdapter thisAdapter = new SqlDataAdapter("select * from userinfo", thisConnection);

        SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter);

        DataSet thisDataSet = new DataSet();

        thisAdapter.Fill(thisDataSet, "userinfo");

        //查询部分代码

        DataColumn[] keys = new DataColumn[1];

        keys[0] = thisDataSet.Tables["userinfo"].Columns["username"];

        thisDataSet.Tables["userinfo"].PrimaryKey = keys;

        string cUserName = "潘万飞";

        DataRow findRow = thisDataSet.Tables["userinfo"].Rows.Find(cUserName);

        if (findRow != null)

        {

            findRow.Delete();

            Response.Write("已将“" + cUserName + "”删除!");

            thisAdapter.Update(thisDataSet, "userinfo");

        }

    }

}