oracle数据库操作(2013-03-26)

关于这个oracle数据库的连接方法，只要稍微修改一下，就可以用作sqlServer或者access的时候，最好将其生成一个类来使用！！！

首先，在web.config中配置数据库连接：

1  <connectionStrings>
2   <add name="ConnectionString" connectionString="Provider=OraOLEDB.Oracle;Data Source=shtcpro;User Id=shtcpro;Password=shtcpro"/>
3   <add name="DbParameter" connectionString="PBCatalogOwner = 'shtcpro',TableCriteria=',shtcpro,''TABLE'',''VIEW'''"/>
4   <add name="Password" connectionString="shtcpro"/>
5   <add name="ServerName" connectionString="shtcpro"/>
6   <add name="UserId" connectionString="shtcpro"/>
7   <add name="AutoCommit" connectionString="false"/>
8  </connectionStrings>

database类：

1 using System.Configuration;
2 using System.Data.OleDb;
3 using System.Data;
4 using System.Web.UI;
5 using System.IO;

  1 /// <summary>
  2     /// sql连接语句
  3     /// </summary>
  4     /// <returns></returns>
  5     public OleDbConnection sqlCon()
  6     {
  7         string connString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
  8         OleDbConnection connection = new OleDbConnection(connString);
  9         return connection;
 10     }
 11     
 12     /// <summary>
 13     /// 执行sql语句
 14     /// </summary>
 15     /// <param name="SqlStr">sql语句</param>
 16     /// <returns>执行结果（true/false）</returns>
 17     public bool execSqlCom(string sqlStr)
 18     {
 19         string connString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
 20         OleDbConnection mycon = new OleDbConnection(connString);
 21         mycon.Open();
 22         OleDbCommand mycom = new OleDbCommand(sqlStr, mycon);
 23         try
 24         {
 25             mycom.ExecuteNonQuery();
 26             return true;
 27         }
 28         catch (Exception e)
 29         {
 30             return false;
 31         }
 32         finally
 33         {
 34             mycon.Close();
 35         }
 36     }
 37 
 38     /// <summary>
 39     /// 获取一个数据集DataSet
 40     /// </summary>
 41     /// <param name="SqlStr">sql语句</param>
 42     /// <returns>一个数据集DataSet</returns>
 43     public DataSet getDataSet(string sqlStr)
 44     {
 45         string connString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
 46         OleDbConnection mycon = new OleDbConnection(connString);
 47         mycon.Open();
 48         OleDbDataAdapter ada = new OleDbDataAdapter(sqlStr, mycon);
 49         DataSet myds = new DataSet();
 50         try
 51         {
 52             ada.Fill(myds);
 53         }
 54         catch (Exception e)
 55         {
 56         }
 57         finally
 58         {
 59             mycon.Close();
 60         }
 61         return myds;
 62     }
 63 
 64     /// <summary>
 65     /// 获取时间
 66     /// </summary>
 67     /// <param name="sqlcmd">sql语句</param>
 68     /// <returns>查询的时间类型的结果</returns>
 69     public DateTime getDateTime(string sqlStr)
 70     {
 71         string connString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
 72         OleDbConnection mycon = new OleDbConnection(connString);
 73         OleDbCommand mycom = new OleDbCommand(sqlStr, mycon);
 74         mycom.Connection.Open();
 75         DateTime result = Convert.ToDateTime("3000-01-01");
 76         try
 77         {
 78             if (mycom.ExecuteScalar() != DBNull.Value)
 79             {
 80                 result = Convert.ToDateTime(mycom.ExecuteScalar());
 81             }
 82         }
 83         catch (Exception e)
 84         {
 85         }
 86         finally
 87         {
 88             mycom.Connection.Close();
 89         }
 90         return result;
 91     }
 92 
 93     /// <summary>
 94     /// 获取查询的字符串
 95     /// </summary>
 96     /// <param name="SqlStr">sql查询语句</param>
 97     /// <returns>查询的字符串的结果</returns>
 98     public string getString(string sqlStr)
 99     {
100         string connString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
101         OleDbConnection mycon = new OleDbConnection(connString);
102         OleDbCommand mycom = new OleDbCommand(sqlStr, mycon);
103         mycom.Connection.Open();
104         string ret = "";
105         try
106         {
107             if (mycom.ExecuteScalar() != DBNull.Value)
108             {
109                 ret = mycom.ExecuteScalar().ToString();
110             }
111         }
112         catch (Exception e)
113         {
114         }
115         finally
116         {
117             mycom.Connection.Close();
118         }
119         return ret;
120     }
121 
122     /// <summary>
123     /// 获取查询的双精度浮点数的结果
124     /// </summary>
125     /// <param name="SqlStr">sql语句</param>
126     /// <returns>查询的双精度浮点数的结果</returns>
127     public Double getDouble(string sqlStr)
128     {
129         string connString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
130         OleDbConnection mycon = new OleDbConnection(connString);
131         OleDbCommand mycom = new OleDbCommand(sqlStr, mycon);
132         mycom.Connection.Open();
133         Double ret = 0;
134         try
135         {
136             if (mycom.ExecuteScalar() != DBNull.Value)
137             {
138                 ret = Convert.ToDouble(mycom.ExecuteScalar());
139             }
140         }
141         catch (Exception e)
142         {
143         }
144         finally
145         {
146             mycom.Connection.Close();
147         }
148         return ret;
149 
150     }
151 
152     /// <summary>
153     /// 获取查询的整形数值的结果
154     /// </summary>
155     /// <param name="SqlStr">sql语句</param>
156     /// <returns>查询的整数的结果</returns>
157     public int getInt32(string sqlStr)
158     {
159         string connString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
160         OleDbConnection mycon = new OleDbConnection(connString);
161         OleDbCommand mycom = new OleDbCommand(sqlStr, mycon);
162         mycom.Connection.Open();
163         int ret = 0;
164         try
165         {
166             if (mycom.ExecuteScalar() != DBNull.Value)
167             {
168                 ret = Convert.ToInt32(mycom.ExecuteScalar());
169             }
170         }
171         catch (Exception e)
172         {
173         }
174         finally
175         {
176         }
177         mycom.Connection.Close();
178         return ret;
179     }
180 
181     /// <summary>
182     /// 客户端弹出自定义的alert脚本对话框
183     /// </summary>
184     /// <param name="msgstr">显示的文本信息</param>
185     public void msg(string msgstr)
186     {
187         Page currentpage = (Page)HttpContext.Current.Handler;
188         msgstr = msgstr.Replace("\'", "'");
189         msgstr = msgstr.Replace("\\'", "'");//把所有的不规范格式化单引号的转义定义去掉，然后重新统一定义转义格式
190         msgstr = msgstr.Replace("'", "\\'");
191         currentpage.ClientScript.RegisterStartupScript(this.GetType(), System.Guid.NewGuid().ToString(), "<script>alert('" + msgstr + "');</script>");
192     }
193 
194     /// <summary>
195     /// 通过文件流下载文件
196     /// </summary>
197     /// <param name="_Response">当前页面Response对象</param>
198     /// <param name="_fileName">保存时的文件名</param>
199     /// <param name="_fullPath">服务器端的完整文件路径</param>
200     /// <returns>下载成功返回true</returns>
201     public bool ResponseFile(HttpResponse _Response, string _fileName, string _fullPath)
202     {
203         FileStream myFile = new FileStream(_fullPath, FileMode.Open, FileAccess.Read, FileShare.Read);
204         BinaryReader br = new BinaryReader(myFile);
205         try
206         {
207             if (myFile.Length > 0)
208             {
209 
210                 _Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(_fileName, System.Text.Encoding.UTF8));
211                 _Response.AppendHeader("Contenttype", "application/pdf");
212                 _Response.AppendHeader("Cache-Control", "must-revalidate, post-check=0, pre-check=0");
213                 //读取数据
214                 long fileLength = myFile.Length;
215                 long startBytes = 0;
216                 int pack = 10240; //10K bytes
217                 br.BaseStream.Seek(startBytes, SeekOrigin.Begin);
218                 int maxCount = (int)Math.Floor((double)((fileLength - startBytes) / pack)) + 1;
219                 int ys = (int)(fileLength % (long)pack);
220                 for (int i = 0; i < maxCount - 1; i++)
221                 {
222                     if (_Response.IsClientConnected)
223                     {
224                         _Response.BinaryWrite(br.ReadBytes(pack));
225                     }
226                     else
227                     {
228                         i = maxCount;
229                     }
230                 }
231                 if (ys > 0)
232                 {
233                     if (_Response.IsClientConnected)
234                     {
235                         _Response.BinaryWrite(br.ReadBytes(ys));
236                     }
237                 }
238                 _Response.Flush();
239                 _Response.Close();
240             }
241         }
242         catch (Exception e)
243         {
244             msg(e.Message);
245             return false;
246         }
247         finally
248         {
249             br.Close();
250             myFile.Close();
251         }
252         return true;
253     }

附件下载

database.cs

posted @ 2013-03-26 10:45 yanb_l 阅读(366) 评论(0) 收藏举报

刷新页面返回顶部

且行且走

生于忧患死于安乐
业精于勤荒于嬉

oracle数据库操作(2013-03-26)

公告

且行且走

生于忧患死于安乐 业精于勤荒于嬉

oracle数据库操作(2013-03-26)

公告

生于忧患死于安乐
业精于勤荒于嬉