数据库操作整理

		

//sqlserver windows登录
//高级导出的连接代码

Data Source=(LocalDB)\v11.0;AttachDbFilename=e:\WebSites\WebSite135\App_Data\abc.mdf;Integrated Security=True
Data Source=PC-20160801OGQX;Initial Catalog=abc;Integrated Security=True;Pooling=False
Data Source=(LocalDB)\v11.0;AttachDbFilename=e:\WebSites\WebSite115\App_Data\abc.mdf;Integrated Security=True
//测试好的代码
<!-- <add name="ConnectionString" connectionString="Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\abc.mdf;Integrated Security=True"/>-->
 

//sqlserver sa 登录

Data Source=(LocalDB)\v11.0;AttachDbFilename=e:\WebSites\WebSite135\App_Data\abc.mdf;User ID=sa
Data Source=PC-20160801OGQX;Initial Catalog=abc;User ID=sa;Pooling=False
Data Source=(LocalDB)\v11.0;AttachDbFilename=e:\WebSites\WebSite115\App_Data\abc.mdf;User ID=sa
//测试好的代码
<!--   <add name="ConnectionString" connectionString="Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\abc.mdf;User ID=sa;Integrated Security=True"/>-->

//web.config sqlserver 连接
<connectionStrings>
    <add name="ConnectionString" connectionString="Data Source=.\SQLEXPRESS;
AttachDbFilename=|DataDirectory|\mydatabase.mdf;Integrated Security=True;User Instance=True"/>
  </connectionStrings>

//------------------------数据库-----空间--------
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
------------------------数据库-----空间--------
//连接测试程序
 protected void Page_Load(object sender, EventArgs e)
    {
        //从web.config配置文件取出数据库连接串
        string sqlconnstr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        //建立数据库连接对象
        SqlConnection sqlconn = new SqlConnection(sqlconnstr);
        //打开连接对象
        sqlconn.Open();
        Label1.Text = "成功建立SQL Server 2012数据库连接";
        //关闭连接对象
        sqlconn.Close();
        sqlconn = null;
    }
    
    <!--
    
     <add name="BDOAConnectionString" connectionString="Data Source=.;Initial Catalog=CSPostOA;User ID=sa;Password=;Integrated Security=True" providerName="System.Data.SqlClient"/>
     
     -->
     
     //------------数据库绑定---------------------
      //查询student数据库获取结果集ds
            string sqlconnstr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            DataSet ds = new DataSet();
            using (SqlConnection sqlconn = new SqlConnection(sqlconnstr))
            {
                SqlDataAdapter sqld = new SqlDataAdapter("select * from student", sqlconn);
                sqld.Fill(ds, "tabstudent");
            }
            //以数据集中名为tabstudent的DataTable作为数据源，为控件绑定数据
            GridView1.DataSource = ds.Tables["tabstudent"].DefaultView;
            GridView1.DataBind();
            
            
            //-------------绑定函数---------------------------
             void bindgrid()
        {
            string sqlconnstr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; ;
            DataSet ds = new DataSet();
            using (SqlConnection sqlconn = new SqlConnection(sqlconnstr))
            {
                SqlDataAdapter sqld = new SqlDataAdapter("select * from student", sqlconn);
                sqld.Fill(ds, "tabstudent");
            }
            //判断是否已经进行排序，如果是则按照ViewState中存储的信息生成排序后的DataView对象
            if (ViewState["SortDirection"] == null)
                GridView1.DataSource = ds.Tables["tabstudent"].DefaultView;
            else
            {
                DataView SortedDV = new DataView(ds.Tables["tabstudent"]);
                SortedDV.Sort = ViewState["SortExpression"].ToString() + " " + ViewState["SortDirection"].ToString();
                GridView1.DataSource = SortedDV;
            }
            GridView1.DataBind();
        }
        
        //----------------删除-------------------
         protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            //设置数据库连接
            string sqlconnstr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; ;
            SqlConnection sqlconn = new SqlConnection(sqlconnstr);
            sqlconn.Open();
            //删除行处理
            String sql = "delete from student where No='" + GridView1.DataKeys[e.RowIndex][0].Value.ToString() + "'";
            SqlCommand Comm = new SqlCommand(sql, sqlconn);
            Comm.ExecuteNonQuery();
            sqlconn.Close();
            sqlconn = null;
            Comm = null;
            GridView1.EditIndex = -1;
            bindgrid();
        }
        //--------------书上删除代码修改------------------
        //删除行处理
            String sql = "delete from student where No='" + GridView1.DataKeys[e.RowIndex][0].ToString() + "'";
            SqlCommand Comm = new SqlCommand(sql, sqlconn);
            Comm.ExecuteNonQuery();
        
        
        
         //----------------修改-------------------
        
         protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
        {
            string sqlconnstr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; ;
            SqlConnection sqlconn = new SqlConnection(sqlconnstr);
            //提交行修改
            sqlconn.Open();
            SqlCommand Comm = new SqlCommand();
            Comm.Connection = sqlconn;
            Comm.CommandText = "update student set Name=@Name,birthday=@birthday,Adress=@Adress where No=@No";
            Comm.Parameters.AddWithValue("@No", GridView1.DataKeys[e.RowIndex].Value.ToString());
            Comm.Parameters.AddWithValue("@Name", ((TextBox)GridView1.Rows[e.RowIndex].Cells[1].Controls[0]).Text);
            Comm.Parameters.AddWithValue("@birthday", ((TextBox)GridView1.Rows[e.RowIndex].Cells[2].Controls[0]).Text.ToString());
           // Comm.Parameters.AddWithValue("@birthday", "");
            Comm.Parameters.AddWithValue("@Adress", ((TextBox)GridView1.Rows[e.RowIndex].Cells[3].Controls[0]).Text);
            Comm.ExecuteNonQuery();
            sqlconn.Close();
            sqlconn = null;
            Comm = null;
            GridView1.EditIndex = -1;
            bindgrid();
        }
        //-------------分页-------------------
         protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
              {
                  //设置要显示的页的索引并重新绑定数据
                  GridView1.PageIndex = e.NewPageIndex;
                  bindgrid();
              }
        
        
        //-------------绑定分页处理-----------------
        
          //分页数据绑定前设置当前页信息
            Label2.Text = "共" + (GridView1.PageCount).ToString() + "页";
            Label1.Text = "第" + (GridView1.PageIndex + 1).ToString() + "页";
            Label3.Text = string.Format("总页数：{0}，当前页：{1}", GridView1.PageCount, GridView1.PageIndex + 1);
            
        //------------gridview绑定----必须-------------
                GridView2.DataSource = ds.Tables["tabstudent"].DefaultView;
           
                 GridView1.DataBind();
         
         //------------DataList1绑定----必须-------------
          protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack) listbind();
        }
        void listbind()
        {
            string sqlconnstr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; ;
            DataSet ds = new DataSet();
            using (SqlConnection sqlconn = new SqlConnection(sqlconnstr))
            {
                SqlDataAdapter sqld = new SqlDataAdapter("select * from student", sqlconn);
                sqld.Fill(ds, "tabstudent");
            }
            //以数据集中名为tabstudent的DataTable作为数据源，为控件绑定数据
            DataList1.DataSource = ds.Tables["tabstudent"].DefaultView;
            DataList1.DataBind();
        }
        
        ----------下拉框 数据库绑定---dropdownlist-----
          string sqlconnstr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        DataSet ds = new DataSet();
        SqlConnection sqlconn = new SqlConnection(sqlconnstr);
        SqlDataAdapter sqld = new SqlDataAdapter("select * from student", sqlconn);
        sqld.Fill(ds, "tabstudent");
        DropDownList1.DataSource = ds.Tables["tabstudent"].DefaultView;
        DropDownList1.DataValueField = ds.Tables["tabstudent"].Columns[1].ColumnName;
        DropDownList1.DataTextField = ds.Tables["tabstudent"].Columns[1].ColumnName;
        DropDownList1.DataBind();
        //ds.Dispose();
        
        -----------------------------------------------------
        
        --------grid显示---------
               //查询student数据库获取结果集ds
            string sqlconnstr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            DataSet ds = new DataSet();
            using (SqlConnection sqlconn = new SqlConnection(sqlconnstr))
            {
                SqlDataAdapter sqld = new SqlDataAdapter("select * from student", sqlconn);
                sqld.Fill(ds, "tabstudent");
            }
            //以数据集中名为tabstudent的DataTable作为数据源，为控件绑定数据
            GridView1.DataSource = ds.Tables["tabstudent"].DefaultView;
            GridView1.DataBind();
            //label中显示运行状态
            Label1.Text = "查找成功";
            -----------------------------
              //从web.config配置文件取出数据库连接串
            string sqlconnstr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            DataSet ds = new DataSet();
            SqlConnection sqlconn = new SqlConnection(sqlconnstr);
            SqlDataAdapter sqld = new SqlDataAdapter("select * from student", sqlconn);
            sqld.Fill(ds, "tabstudent");
            GridView1.DataSource = ds.Tables["tabstudent"].DefaultView;
            GridView1.DataBind();
            //label中显示运行状态
            Label1.Text = "查找成功";
            
            
----------------------------------------------------
---------------写普通类---------
/// <summary>
/// 帮助类KeyValueClass的定义，其对象存储一个WebSiteName：WebSiteUrl
/// 的键值对，被添加至作为数据源的ArrayList中
/// </summary>
using System;
public class KeyValueClass
{
    private String WebSiteName;
    private String WebSiteUrl;
    public String Name
    {
        get { return WebSiteName; }
        set { WebSiteName = value; }
    }
    public String Url
    {
        get { return WebSiteUrl; }
        set { WebSiteUrl = value; }
    }
    public KeyValueClass(String name, String url)
    {
        this.WebSiteName = name;
        this.WebSiteUrl = url;
    }
}

-------end--------写普通类---------

------------------哈希表操作---------------
//定义哈希表
        Hashtable DataSourceForCBL = new Hashtable(3);
 //初始化哈希表DataSourceForCBL
                this.DataSourceForCBL.Add("徐明明", "男");
                this.DataSourceForCBL.Add("韩旭", "女");
                this.DataSourceForCBL.Add("陈静", "女");
   //为DropDownList绑定数据
                this.DropDownList1.DataSource = this.DataSourceForDDL;
                this.DropDownList1.DataBind();


--------gridview--------添加图片------

                                <asp:TemplateField HeaderText="图片">
                                     <ItemTemplate>
                                <asp:Image ID="Image2" runat="server" ImageUrl='<%#"images/"+Eval("photo") %>' Width="139px"
                                     Height="130px" />
                                  </ItemTemplate>

                                </asp:TemplateField>
                                
  -----gridviw 样版---------------
  <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" Width="100%"
            BorderStyle="None" OnRowDeleting="GridView1_RowDeleting" DataKeyNames="id" BackColor="#3E97B9"
            CellPadding="0" CellSpacing="1" GridLines="None">
            <Columns>
                <asp:BoundField DataField="id" HeaderText="编号"></asp:BoundField>
                <asp:TemplateField HeaderText="名称">
                    <ItemTemplate>
                        <%#Eval("title").ToString() %>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="图片">
                    <ItemTemplate>
                        <asp:Image ID="Image2" runat="server" ImageUrl='<%#"uping/"+Eval("img") %>' Width="139px"
                            Height="130px" />
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="修改">
                    <ItemTemplate>
                        <a href="add_members.aspx?id=<%#Eval("id") %>&type=<%=Request.QueryString["type"].ToString() %>">
                            修改</a>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:CommandField HeaderText="删除" DeleteText="&lt;div id=&quot;de&quot; οnclick=&quot;JavaScript:return confirm('你确定要删除?')&quot;&gt;删除&lt;/div&gt;"
                    ShowDeleteButton="True"></asp:CommandField>
            </Columns>
            <RowStyle HorizontalAlign="Center" />
            <HeaderStyle HorizontalAlign="Center" CssClass="tb" BorderStyle="None" />
        </asp:GridView>
        <webdiyer:AspNetPager ID="AspNetPager1" runat="server" CustomInfoTextAlign="Center"
            FirstPageText="首页" HorizontalAlign="Center" LastPageText="最后一页" NextPageText="下一页"
            PrevPageText="上一页" UrlPaging="True" Width="100%" NumericButtonTextFormatString="[{0}]"
            OnPageChanging="AspNetPager1_PageChanging">
        </webdiyer:AspNetPager>
        
        ----------------书上例子----------
         protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack) bindgrid();

        }
        void bindgrid()
        {
            //查询student数据库获取结果集ds
            string sqlconnstr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            DataSet ds = new DataSet();
            using (SqlConnection sqlconn = new SqlConnection(sqlconnstr))
            {
                SqlDataAdapter sqld = new SqlDataAdapter("select * from student", sqlconn);
                sqld.Fill(ds, "tabstudent");
            }
            //以数据集中名为tabstudent的DataTable作为数据源，为控件绑定数据
            GridView1.DataSource = ds.Tables["tabstudent"].DefaultView;
            GridView1.DataBind();
        }

        protected void GridView1_DataBinding(object sender, EventArgs e)
        {
            //分页数据绑定前设置当前页信息
            Label2.Text = "共" + (GridView1.PageCount).ToString() + "页";
            Label1.Text = "第" + (GridView1.PageIndex + 1).ToString() + "页";
            Label3.Text = string.Format("总页数：{0}，当前页：{1}", GridView1.PageCount, GridView1.PageIndex + 1);
            
        }      

        protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            //设置要显示的页的索引并重新绑定数据
            GridView1.PageIndex = e.NewPageIndex;
            bindgrid();
        }
    }
}

--------------jquery代码------------------
<body>
    <form id="form1" runat="server">
    <div id="box">
        <h1>图片画廊</h1>
        <p><img id="largeImg" src="images/image1.jpg" title="露天电影" alt="图片无法显示" /></p>
        <p class="thumbs">
        <a href="images/image1.jpg" title="露天电影"><img src="images/image1.jpg"/></a>
        <a href="images/image2.jpg" title="一个人发呆"><img src="images/image2.jpg" /></a>
        <a href="images/image3.jpg" title="公交车旁"><img src="images/image3.jpg" /></a>
        <a href="images/image4.jpg" title="高耸云端"><img src="images/image4.jpg" /></a>
        <a href="images/image5.jpg" title="悬空的船"><img src="images/image5.jpg" /></a>
        </p>
    </div>
    </form>
    <script type="text/javascript">
    $(function(){// 页面初始化激活函数
        $(".thumbs a").mouseover(function(){ // 鼠标经过缩微图时的处理函数
            var path = $(this).attr("href"); //获取缩微图的地址信息
            var title = $(this).attr("title"); //获取缩微图的提示信息
            $(this).children().css("border-color","#FF9900");  //设置缩微图内超链接样式
            //把缩微图的地址和提示信息赋予给大图
            $("#largeImg").attr({ src:path,title:title});
        });
        $(".thumbs a").mouseout(function(){ // 鼠标移开缩微图时的处理函数
            $(this).children().css("border-color", "#C0C0C0"); // 恢复缩微图的默认边框颜色
        });
    });
    </script>
</body>