第十章：数据库技术                                  1:如何将数据库的数据填充到XML文件中                                  2:如何利用Web.Config配置数据库的连接字符串                                  3:如何编程使用存储过程                                  4:三层结构的数据层的封装                                  5:如何读取Excel表格中的数据                                  6:如何备份与还原数据库                                  7:数据库中Ajax的实现                                   8:数据库的报表打印                                  9:如何生成验证码                                                                    如何将数据库的数据填充到XML文件中                                    A)生成一个Table对像                                    B)指定Table对像的TablName属性                                    C)调用Table对像的dt.WriteXml(Server.MapPath("chen.xml"));                                                                   如何利用Web.Config配置数据库的连接字符串                           在某些情况下，数据库的连接字符串会经常变动，例如：主机修改了密码。如果                           把连接字符串放在代码中，则必须要编译整个项目，这个时候可以使用Web.Config                           来存储数据库的字符串。Web.Config是一个以Xml形式的文件                           1)在<connectionStrings>节下面配置如下：                                   <connectionStrings>                                     <add name="constr" connectionString="server=.;database=test;uid=sa;pwd=123"  providerName="System.Data.SqlClient">                                          </add>                                   </connectionStrings>                             2)读取配置的字符串：                                    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;                                                                                                 如何编程使用存储过程                  访问存储过程的步骤：                    1)没有结果集的                         A)没有参数的       public static void RunNoneResultProc(string ProcName)                               {                                 SqlCommand Scmd = new SqlCommand(ProcName, _Conn);                                _Conn.Open();                                 Scmd.CommandType = CommandType.StoredProcedure;                                 Scmd.ExecuteNonQuery();                                _Conn.Close();                              }                           B)有参数的           public static void RunNoneResultProc(string ProcName, SqlParameter[] SqlParameters)                  {                    SqlCommand Scmd = new SqlCommand(ProcName, _Conn);                   _Conn.Open();                    Scmd.CommandType = CommandType.StoredProcedure;                    foreach (SqlParameter Item in SqlParameters)                      {                       Scmd.Parameters.Add(Item);                      }                    Scmd.ExecuteNonQuery();                   _Conn.Close();               }           2)有结果集的           public static DataTable RunHasResultProc(string ProcName)                  //没有参数，有返回结果的存储过程            {             _Conn.Open();             SqlCommand Scmd = new SqlCommand(ProcName, _Conn);             Scmd.CommandType = CommandType.StoredProcedure;                                    SqlDataAdapter Sda = new SqlDataAdapter(Scmd);             DataTable Dt = new DataTable();             Sda.Fill(Dt);             _Conn.Close();             return Dt;                      }        public static DataTable RunHasResultProc(string ProcName, SqlParameter[] SqlParameters)   //有参数，有返回结果的存储过程        {            _Conn.Open();            SqlCommand Scmd = new SqlCommand(ProcName, _Conn);                        Scmd.CommandType = CommandType.StoredProcedure;            foreach (SqlParameter Item in SqlParameters)            {                Scmd.Parameters.Add(Item);            }            SqlDataAdapter Sda = new SqlDataAdapter(Scmd);            DataTable Dt = new DataTable();            Sda.Fill(Dt);            _Conn.Close();            return Dt;        }                                          说明：                       A: 可以使用重载的方式来完成                       B: 可以表现为对数据库的增，删，改。操作                       C: 举一个可以把职工的工资加上100的存储过程                       D: 举一个可以把满足条件的职工的工资加上指定工资的存储过程                       E: 举一个有输出参数的存储过程                                                             三层结构的数据层的封装   public class ClsDataOper    {        private static SqlConnection _Conn = new SqlConnection("server=.;database=northwind;uid=sa;pwd=;");        public static DataTable RunHasResultSql(string sql) //执行有返回结果集的sql语句        {           try            {                SqlDataAdapter _Sada = new SqlDataAdapter(sql, _Conn);                _Conn.Open();                DataTable Dt = new DataTable();                _Sada.Fill(Dt);                               _Conn.Close();                                return Dt;            }           catch (Exception e)            {               MessageBox.Show("返回结果集出错！", "消息", MessageBoxButtons.OK, MessageBoxIcon.Error);                return null;           }        }         public static void RunNoneResultSql(string sql) //执行没有返回结果集的sql语句        {            try            {                SqlCommand Scmd = new SqlCommand(sql, _Conn);                _Conn.Open();                Scmd.ExecuteNonQuery();                _Conn.Close();            }            catch (Exception e)            {                MessageBox.Show("执行命令失败！", "消息", MessageBoxButtons.OK, MessageBoxIcon.Error);                            }        }        public static SqlParameter GetParameter(string ParameterName,  SqlDbType ParameterType, object ParameterValue)        {            SqlParameter p = new SqlParameter();            p.ParameterName = ParameterName;            p.SqlDbType = ParameterType;            p.Value = ParameterValue;            return p;       }public static SqlParameter GetParameter(string ParameterName, SqlDbType ParameterType, object ParameterValue,bool IsOutPut)        {            SqlParameter p = new SqlParameter();            p.ParameterName = ParameterName;            p.SqlDbType = ParameterType;            p.Value = ParameterValue;            if (IsOutPut)            {                p.Direction = ParameterDirection.Output;            }            return p;        }         public static void RunNoneResultProc(string ProcName) //没有参数，没有返回结果的存储过程        {            SqlCommand Scmd = new SqlCommand(ProcName, _Conn);            _Conn.Open();            Scmd.CommandType = CommandType.StoredProcedure;            Scmd.ExecuteNonQuery();            _Conn.Close();        }         public static void RunNoneResultProc(string ProcName, SqlParameter[] SqlParameters) //有参数，没有返回结果的存储过程        {            SqlCommand Scmd = new SqlCommand(ProcName, _Conn);            _Conn.Open();            Scmd.CommandType = CommandType.StoredProcedure;            foreach (SqlParameter Item in SqlParameters)            {                Scmd.Parameters.Add(Item);            }            Scmd.ExecuteNonQuery();            _Conn.Close();        }         public static DataTable RunHasResultProc(string ProcName) //没有参数，有返回结果的存储过程        {            _Conn.Open();            SqlCommand Scmd = new SqlCommand(ProcName, _Conn);            Scmd.CommandType = CommandType.StoredProcedure;                                   SqlDataAdapter Sda = new SqlDataAdapter(Scmd);            DataTable Dt = new DataTable();            Sda.Fill(Dt);            _Conn.Close();            return Dt;                    }         public static DataTable RunHasResultProc(string ProcName, SqlParameter[] SqlParameters) //有参数，有返回结果的存储过程        {            _Conn.Open();            SqlCommand Scmd = new SqlCommand(ProcName, _Conn);                        Scmd.CommandType = CommandType.StoredProcedure;            foreach (SqlParameter Item in SqlParameters)            {                Scmd.Parameters.Add(Item);            }            SqlDataAdapter Sda = new SqlDataAdapter(Scmd);            DataTable Dt = new DataTable();            Sda.Fill(Dt);            _Conn.Close();            return Dt;        }    }                 如何读取Excel表格中的数据                             1:加入Html的File控件，并转换成为Web服务器控件                             2:写入以下代码：                                    OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+this.File1.Value+";Extended Properties='excel 8.0'");                                    OleDbDataAdapter ada = new OleDbDataAdapter("select * from [Sheet1$]", con);                                    DataTable dt=new DataTable();                                    ada.Fill(dt);                                    this.GridView1.DataSource =dt;                                    this.GridView1.DataBind();                                  如何备份与还原数据库                                  1:备份sql语句：                                     backup database 数据库 to disk=路径                                  2:还原数据库：                                     restore database from disk=路径                                   8:数据库的报表打印                                      1)可以通过导出Excel来实现报表的打印                                      2)通过水晶报表来实现打印：                                         A)拖一个CrystalReportViewer到页面上                                         B)选择CrystalReportViewer控件的属性：ReportSourceId，并选择"新建报表源..."                                         C)在弹出的对话框中输入报表的数据源控件的名一般为"CrystalReportSource1",与报表                                         D)添加一个数据集，在设计视图中选择"数据库专家",并加载数据集                                         E)在Page_Load事件里面写上代码：                                                   DataSet1 ds1 = new DataSet1();                    ds1.Tables.Clear();                                                   DataTable dt = DataOper.GetDataTable("select * from dbo.chen");                                                   dt.TableName = "chen";                                                   ds1.Tables.Add(dt);                                                   this.CrystalReportSource1.ReportDocument.SetDataSource(ds1);                                                   this.CrystalReportSource1.DataBind();                                                                                            9:如何生成验证码                        为什么要生成验证码：                          如果只用普通的用户名和密码进行身份验证有一些弊端。原因在于用户名和密码                          一般不会改变。暴力破解软件来对密码进行破解。具体原理其实是很简单的，这个软件会想服务器不断的                          提交密码然后尝试登陆，提交的密码是根据字典（所谓黑客字典）或者是直接的把键盘上所有的键随意的所有组合全部尝试一遍      这样尝试了所有的组合之后你的密码就会被猜到，这样的破解方法一般称为暴力破解。但是现在采用验证码技术之后，                          每一次向服务器提交登陆申请的时候不仅仅要输入密码还要输入验证码，而这个验证码是随机变化的,更重要的是验证码不是文本,而是                          可是做成图片之后,这样黑客软件如果想读取图片的数字，几乎是不可能                        如何生成检证码：                            两个页面：                              1)用于产生验证码。假设为CreateCheckCode.aspx                                     private void CreateCheckCodeImage(string checkCode)    {  //将验证码生成图片显示        if (checkCode == null || checkCode.Trim() == String.Empty)            return;        System.Drawing.Bitmap image = new System.Drawing.Bitmap((int)Math.Ceiling((checkCode.Length * 12.5)), 22);        Graphics g = Graphics.FromImage(image);            //生成随机生成器             Random random = new Random();            //清空图片背景色             g.Clear(Color.White);            //画图片的背景噪音线             for (int i = 0; i < 25; i++)            {                int x1 = random.Next(image.Width);                int x2 = random.Next(image.Width);                int y1 = random.Next(image.Height);                int y2 = random.Next(image.Height);                 g.DrawLine(new Pen(Color.Silver), x1, y1, x2, y2);            }            Font font = new System.Drawing.Font("Arial", 12, (System.Drawing.FontStyle.Bold | System.Drawing.FontStyle.Italic));            System.Drawing.Drawing2D.LinearGradientBrush brush = new System.Drawing.Drawing2D.LinearGradientBrush(new Rectangle(0, 0, image.Width, image.Height), Color.Blue, Color.DarkRed, 1.2f, true);            g.DrawString(checkCode, font, brush, 2, 2);            //画图片的前景噪音点             for (int i = 0; i < 100; i++)            {                int x = random.Next(image.Width);                int y = random.Next(image.Height);                 image.SetPixel(x, y, Color.FromArgb(random.Next()));            }            //画图片的边框线             g.DrawRectangle(new Pen(Color.Silver), 0, 0, image.Width - 1, image.Height - 1);            System.IO.MemoryStream ms = new System.IO.MemoryStream();            image.Save(ms, System.Drawing.Imaging.ImageFormat.Gif);                                   Response.ClearContent();            Response.ContentType = "image/Gif";            Response.BinaryWrite(ms.ToArray());            }                             2)用于实现验证码：test.aspx。                 在里面放入一个html的img标记，形如：<img src=CreateCheckCode.aspx>              3)实现检验码的刷新：                           function change()                                 {                                   document.getElementById("img1").src ="default3.aspx?id="+Math.random();                                 }    

评论