Asp.NetDBクラス
23999 ワード
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Web;
using System.Collections;
using System.Configuration;
using System.Drawing;
using System.IO;
using System.Data.SqlClient;
namespace DBTool
{
public class DB
{
public static OleDbConnection conn = null;
public static string GetConnStringOut()
{
string s = System.Configuration.ConfigurationManager.ConnectionStrings["smconn"].ConnectionString.ToString();
return s;
}
public static string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
public string GetConnString()
{
string s = System.Configuration.ConfigurationManager.ConnectionStrings["smconn"].ConnectionString.ToString();
return s;
}
/// <summary>
///
/// </summary>
public void OpenConn()
{
try
{
//if (conn == null)
//{
conn = new OleDbConnection();
conn.ConnectionString = this.GetConnString();
// }
//if (conn != null && conn.State == ConnectionState.Closed)
conn.Open();
}
catch
{
}
}
/// <summary>
///
/// </summary>
public void CloseConn()
{
if (conn != null && conn.State == ConnectionState.Open)
conn.Close();
conn.Dispose();
}
/// <summary>
/// DataTable
/// </summary>
/// <param name="sql"> sql </param>
/// <returns>DataTable</returns>
public DataTable RunSqlGetDataTable(string sql)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
DataSet ds = new DataSet();
try
{
connection.Open();
SqlDataAdapter command = new SqlDataAdapter(sql, connection);
command.Fill(ds, "ds");
}
catch (System.Data.SqlClient.SqlException ex)
{
throw new Exception(ex.Message);
}
return ds.Tables[0];
}
}
/// <summary>
///
/// </summary>
/// <param name="sql"> sql </param>
/// <returns> int </returns>
public int RunSqlGetRowCount(string sql)
{
OpenConn();
OleDbCommand cmd = new OleDbCommand(sql, conn);
int rowcount = -1;
try
{
rowcount = cmd.ExecuteNonQuery();
}
catch (OleDbException ex)
{
string sTmp = ex.Message.ToLower();
if (sTmp.IndexOf(" ") >= 0)
{
//MessageBox.Show(" !");
}
else if (sTmp.IndexOf("reference") >= 0)
{
//MessageBox.Show(" !");
}
else
{
rowcount = -3;//
}
}
catch { }
CloseConn();
return rowcount;
}
/// <summary>
/// Execute a SqlCommand that returns a resultset against the database specified in the connection string
/// using the provided parameters.
/// </summary>
/// <param name="connectionString"> </param>
/// <param name="cmdType">SqlCommand ( ， T-SQL ， 。)</param>
/// <param name="cmdText"> T-SQL </param>
/// <param name="commandParameters"> SqlCommand </param>
/// <returns>A SqlDataReader containing the results</returns>
public static SqlDataReader ExecuteReader(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(connectionString);
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return rdr;
}
catch (Exception ex)
{
conn.Close();
throw new Exception(ex.Message);
}
}
private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
{
//
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
//
if (trans != null)
cmd.Transaction = trans;
cmd.CommandType = cmdType;
if (cmdParms != null)
{
foreach (SqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
}
/// <summary>
///
/// </summary>
/// <param name="sql"> sql id</param>
/// <returns> int </returns>
public int RunSqlGetRowCountReturnId(string sql)
{
OpenConn();
OleDbCommand cmd = new OleDbCommand(sql, conn);
object rowcount = "0";
try
{
rowcount = cmd.ExecuteScalar();
}
catch (OleDbException ex)
{
string sTmp = ex.Message.ToLower();
if (sTmp.IndexOf(" ") >= 0)
{ //MessageBox.Show(" !");
}
else if (sTmp.IndexOf("reference") >= 0)
{ //MessageBox.Show(" !");
}
else
{
rowcount = -3;//
}
}
catch { }
CloseConn();
return int.Parse(rowcount.ToString());
}
/// <summary>
///
/// </summary>
/// <param name="sql"> </param>
/// <returns>object</returns>
///
public object RunSqlGetObject(string sql)
{
OpenConn();
OleDbCommand cmd = new OleDbCommand(sql, conn);
object o = cmd.ExecuteScalar();
CloseConn();
return o;
}
///
/// <summary>
///
/// </summary>
/// <param name="tconn"></param>
/// <param name="transaction"></param>
public void OpenTransConn(ref OleDbConnection tconn, ref OleDbTransaction transaction)
{
try
{
if (tconn == null)
{
tconn = new OleDbConnection();
tconn.ConnectionString = this.GetConnString();
}
if (tconn != null && tconn.State == ConnectionState.Closed)
{
tconn.Open();
transaction = tconn.BeginTransaction();
}
}
catch
{
}
}
/// <summary>
///
/// </summary>
public void CloseTransConn(ref OleDbConnection tconn, ref OleDbTransaction transaction)
{
if (transaction != null)
{
transaction.Commit();//
}
if (tconn != null && tconn.State == ConnectionState.Open)
tconn.Close();
}
public bool RunTransSqlGetBool(ref OleDbConnection tconn, ref OleDbTransaction transaction, string sql)
{
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = tconn;
cmd.CommandText = sql;
cmd.Transaction = transaction;
int rowcount = -1;
try
{
rowcount = cmd.ExecuteNonQuery();
}
catch (OleDbException ex)
{
if (ex.ErrorCode == -2147217873)
{
//MessageBox.Show(" !");
}
else
{
//MessageBox.Show(" !");
}
transaction.Rollback();
return false;
}
catch { }
if (rowcount > 0)
return true;
return false;
}
//
public bool RunTransSqls(ArrayList sqlAl)
{
int rowcount = -1;
OleDbConnection conn = new OleDbConnection();
conn.ConnectionString = this.GetConnString();
OleDbTransaction tran = null;
OleDbCommand cmd = null;
#region MyRegion
try
{
conn.Open();
tran = conn.BeginTransaction();
cmd = new OleDbCommand();
cmd.Connection = conn;
cmd.Transaction = tran;
cmd.CommandType = CommandType.Text;
foreach (string sql in sqlAl)
{
//MessageBox.Show(sql);
rowcount = -1;
cmd.CommandText = sql;
rowcount = cmd.ExecuteNonQuery();
if (rowcount <= 0)// ，
{
//tran.Rollback();
//return false;
}
}
tran.Commit();
return true;
}
catch (Exception ex)
{
tran.Rollback();
//MessageBox.Show(ex.Message);
}
finally
{
try
{
if (conn != null && conn.State == ConnectionState.Open)
conn.Close();
if (conn != null)
conn.Dispose();
}
catch { }
}
#endregion
return false;
}
//
public int RunProcGetReturnValue(string procName, ArrayList al)
{
int re = 0;
//
OpenConn();
try
{
//
#region
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;//
cmd.CommandText = procName;//
cmd.CommandType = CommandType.StoredProcedure;//
#endregion
cmd.Parameters.Clear();
// return ,
// ：
OleDbParameter paramReturn = new OleDbParameter("returnvalue", OleDbType.VarChar, 50);
paramReturn.Direction = ParameterDirection.ReturnValue;// ；
paramReturn.Value = -1;//
cmd.Parameters.Add(paramReturn);// cmd
foreach (OleDbParameter p in al)
{
cmd.Parameters.Add(p);
}
cmd.ExecuteNonQuery();
re = Convert.ToInt32(paramReturn.Value);
}
catch { }
finally
{
CloseConn();
}
return re;
}
public int RunProcGetOutputValue(string procName, ArrayList al)
{
//
OpenConn();
//
#region
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;//
cmd.CommandText = procName;//
cmd.CommandType = CommandType.StoredProcedure;//
#endregion
cmd.Parameters.Clear();
foreach (OleDbParameter p in al)
{
cmd.Parameters.Add(p);
}
try
{
return cmd.ExecuteNonQuery(); ;
}
catch
{
return 0;
}
finally
{
CloseConn();
}
}
// （ ）
public DataSet RunProcGetDataTable(string procName)
{
//
OpenConn();
//
#region
ArrayList al = new ArrayList();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;//
cmd.CommandText = procName;//
cmd.CommandType = CommandType.StoredProcedure;//
#endregion
cmd.Parameters.Clear();
foreach (OleDbParameter p in al)
{
cmd.Parameters.Add(p);
}
try
{
DataSet ds = new DataSet();
OleDbDataAdapter da = new OleDbDataAdapter();
da.SelectCommand = cmd;
da.Fill(ds);
return ds;
}
catch
{
return null;
}
finally
{
CloseConn();
}
}
// （ ）
public DataTable RunSqlGetDataTableFY(string name, int id)
{
OpenConn();
OleDbDataAdapter da = null;
DataTable dt = null;
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
cmd.CommandText = name; ;
cmd.CommandType = CommandType.StoredProcedure;
OleDbParameter p2 = new OleDbParameter("@Id", OleDbType.Integer, 4);
p2.Value = id;
cmd.Parameters.Add(p2);
try
{
da = new OleDbDataAdapter();
da.SelectCommand = cmd;
dt = new DataTable();
da.Fill(dt);
}
catch { }
CloseConn();
return dt;
}
public static OleDbConnection Getconn()
{
try
{
if (conn != null && conn.State == ConnectionState.Closed)
conn.Open();
}
catch
{
}
return conn;
}
/// <summary>
/// csv
/// </summary>
public static bool DataDownTOcsv(string sql, string filename, string tableheader1, string tableheader2, string columname, int columcount)
{
try
{
string strFileToOrg = "", strBufferLine = "", strBufferLine1 = "";
int i;
strFileToOrg = HttpContext.Current.Server.MapPath("~/pdf/" + filename + ".csv");
StreamWriter strmWriterObj = new StreamWriter(strFileToOrg, false, System.Text.Encoding.Default);//
//OleDbConnection OleDbConnection1=new OleDbConnection();
OleDbConnection OleDbConnection1 = DB.Getconn();
//OleDbConnection1.ConnectionString="File Name="+HttpContext.Current.Server.MapPath("conn.udl");
//OleDbConnection1.Open();
OleDbCommand cmdGenFile = new OleDbCommand();
cmdGenFile.Connection = OleDbConnection1;
cmdGenFile.CommandText = sql;
OleDbDataReader drGenFile = cmdGenFile.ExecuteReader();
strmWriterObj.WriteLine(tableheader1);
strmWriterObj.WriteLine(tableheader2);
strmWriterObj.WriteLine(columname);
while (drGenFile.Read())
{
strBufferLine = "";
strBufferLine1 = Convert.ToString(drGenFile.GetValue(0));
strBufferLine = strBufferLine1;
for (i = 1; i <= (columcount - 1); i++)
{
strBufferLine1 = "";
strBufferLine1 = Convert.ToString(drGenFile.GetValue(i));
strBufferLine = strBufferLine + "," + strBufferLine1;
}
strmWriterObj.WriteLine(strBufferLine);
}
strmWriterObj.Close();
drGenFile.Close();
return true;
}
catch
{
return false;
}
}
/// <summary>
///
/// </summary>
/// <param name="originalImagePath"> （ ）</param>
/// <param name="thumbnailPath"> （ ）</param>
/// <param name="width"> </param>
/// <param name="height"> </param>
/// <param name="mode"> </param>
public bool MakeThumbnail(string originalImagePath, string thumbnailPath, int width, int height, string mode)
{
System.Drawing.Image originalImage = System.Drawing.Image.FromFile(originalImagePath);
int towidth = width;
int toheight = height;
int x = 0;
int y = 0;
int ow = originalImage.Width;
int oh = originalImage.Height;
switch (mode)
{
case "HW":// （ ）
break;
case "W":// ，
toheight = originalImage.Height * width / originalImage.Width;
break;
case "H":// ，
towidth = originalImage.Width * height / originalImage.Height;
break;
case "Cut":// （ ）
if ((double)originalImage.Width / (double)originalImage.Height > (double)towidth / (double)toheight)
{
oh = originalImage.Height;
ow = originalImage.Height * towidth / toheight;
y = 0;
x = (originalImage.Width - ow) / 2;
}
else
{
ow = originalImage.Width;
oh = originalImage.Width * height / towidth;
x = 0;
y = (originalImage.Height - oh) / 2;
}
break;
default:
break;
}
// bmp
System.Drawing.Image bitmap = new System.Drawing.Bitmap(towidth, toheight);
//
System.Drawing.Graphics g = System.Drawing.Graphics.FromImage(bitmap);
//
g.InterpolationMode = System.Drawing.Drawing2D.InterpolationMode.High;
// ,
g.SmoothingMode = System.Drawing.Drawing2D.SmoothingMode.HighQuality;
//
g.Clear(System.Drawing.Color.Transparent);
//
g.DrawImage(originalImage, new System.Drawing.Rectangle(0, 0, towidth, toheight),
new System.Drawing.Rectangle(x, y, ow, oh),
System.Drawing.GraphicsUnit.Pixel);
try
{
// jpg
bitmap.Save(thumbnailPath, System.Drawing.Imaging.ImageFormat.Jpeg);
return true;
}
catch (System.Exception e)
{
throw e;
return false;
}
finally
{
originalImage.Dispose();
bitmap.Dispose();
g.Dispose();
}
}
}
}