ASPNET数据库操作大全.docx

《ASPNET数据库操作大全.docx》由会员分享，可在线阅读，更多相关《ASPNET数据库操作大全.docx（5页珍藏版）》请在三一办公上搜索。

1、ASPNET数据库操作大全ASP.NET数据库操作大全 1. 2. 3. 4. 5. ASP.NET 数据库连接 Web.config文件中添加 6. 7. 8. 9. Sql连接字符串 10. string connectionString 11. =ConfigurationManager.ConnectionStringsCreateWord.ConnectionString; 12. SqlConnection con = new SqlConnection(connectionString); 13. con.Open; 14. /自定义查询SQL字符串 15. string cmd

2、text = select vMasterName,vMasterPsd from Master; 16. /数据库记录保存到字符串 17. SqlCommand cmd = new SqlCommand(cmdtext,con); 18. string bb = cmd.ExecuteScalar.ToString; 19. /数据库记录保存到适配器 20. /创建SqlDataAdapter数据适配器 21. SqlDataAdapter sda = new SqlDataAdapter(cmdtext, con); 22. /创建数据集 23. DataSet ds = new Data

3、Set; 24. /填充数据集合 25. sda.Fill(ds,Master); 26. /使用DataReader对象读取数据 27. SqlDataReader dr = cmd.ExecuteReader; 28. if (dr.Read) 29. 30. string abc = dr.ToString; 31. 32. else 33. 34. Response.Write(); 37. 38. dr.Close; 39. con.Close; 40. 41. 42. /检索单条数据库记录ExecuteScalar 43. cmd = new SqlCommand(Select C

4、ount(*) from Master,con); 44. string count = cmd.ExecuteScalar; 45. 46. /数据库查询参数使用 47. cmd = new SqlCommand(Select phone from Authors Where au_fname=firstname And au_lname=lastname, con); 48. string FristName,LastName; 49. cmd.Parameters.Add(firstname,FristName); 50. cmd.Parameters.Add(lastname,Last

5、Name); 51. string phone; 52. phone = cmd.ExecuteScalar; 53. 54. /向数据库添加，删除，更新 55. cmd = new SqlCommand(Insert Products (ProductName,UnitPrice) values (ProductName,UnitPrice),con ); 56. /更新以及删除只需要修改相关SQL语句 57. cmd.Parameters.Add(ProductName,ls); 58. cmd.Parameters.Add(UnitPrice, SqlDbType.Money).Valu

6、e = 13; /转换插入数据库数据格式 59. cmd.ExecuteNonQuery; 60. 61. /使用存储过程 62. /* 63. *Create Procedure InsertProduts 64. * ( 65. * ProductName NVarchar(80), 66. * UnitPrice Money 67. * ) as Insert Products(ProductName , UnitPrice) 68. * Values (ProuctName ,UnitPrice) 69. */ 70. cmd = new SqlCommand(InsertProduc

7、ts,con); 71. /InsertProducts 为SQL数据库里的存储过程名称 72. 73. cmd.CommandType = CommandType.StoredProcedure; 74. cmd.Parameters.Add(ProductName,Milk); 75. cmd.Parameters.Add(UnitPrice,12.45); 76. cmd.ExecuteNonQuery; 77. 78. /获取返回值鹤输出参数 79. 80. /使用返回值 81. /* 82. Create Procedure GetAuthorCount 83. * As 84. *

8、 Return (Select Count(*) From Authors) 85. */ 86. cmd = new SqlCommand(GetAuthorcount,con); 87. cmd.CommandType = CommandType.StoredProcedure; 88. SqlParameter parmReturnVaule; 89. parmReturnVaule = cmd.Parameters.Add(ReturnValue,SqlDbType.Int); 90. parmReturnVaule.Direction = ParameterDirection.Ret

9、urnValue; 91. cmd.ExecuteNonQuery; 92. int i = cmd.Parameters(ReturnValue).Value; 93. 94. /使用输出参数 95. /* 96. Create Procedure GetLastname 97. * ( 98. * firstname Varchar(20), 99. * lastname Varchar(20) Output 100. * ) 101. * As 102. * Select lastname =au_lname 103. * From 104. * Where au_fame =first

10、name 105. */ 106. cmd = new SqlCommand(GetLastname,con); 107. cmd.CommandType = CommandType.StoredProcedure; 108. 109. cmd.Parameters.Add(firstname,Ann); 110. SqlParameter parmLastName = cmd.Parameters.Add(lastname,SqlDbType.VarChar); 111. parmLastName.Size = 40; 112. parmLastName.Direction = ParameterDirection.Output; 113. 114. cmd.ExecuteNonQuery;