連接資料庫公共類

A. c# 連接oracle資料庫的公共類

using System;
using System.Collections.Generic;
using System.Text;

using System.Configuration;
using System.Data.OracleClient;
using System.Data;
using System.Windows.Forms;
using System.Collections;
using Microsoft.VisualBasic.Devices;
using System.Security.Cryptography;
using System.IO;

namespace mydbproc
{
class Encrypt
{
public string MD5(string P_EncryptStr)
{
int num;
byte[] buffer = new byte[P_EncryptStr.Length];
for (num = 0; num < P_EncryptStr.Length; num++)
{
buffer[num] = (byte) P_EncryptStr[num];
}
byte[] buffer2 = new MD5CryptoServiceProvider().ComputeHash(buffer);
string str = "";
for (num = 0; num < buffer2.Length; num++)
{
str = str + buffer2[num].ToString();
}
return str;
}

public string TripleDES(string Value, string password)
{
string s = "";
string str2 = "qcDY6X+aPLw=";
SymmetricAlgorithm algorithm = new ();
algorithm.Key = Convert.FromBase64String(s);
algorithm.IV = Convert.FromBase64String(str2);
algorithm.Mode = CipherMode.ECB;
algorithm.Padding = PaddingMode.PKCS7;
ICryptoTransform transform = algorithm.CreateEncryptor(algorithm.Key, algorithm.IV);
byte[] bytes = Encoding.UTF8.GetBytes(Value);
MemoryStream stream = new MemoryStream();
CryptoStream stream2 = new CryptoStream(stream, transform, CryptoStreamMode.Write);
stream2.Write(bytes, 0, bytes.Length);
stream2.FlushFinalBlock();
stream2.Close();
return Convert.ToBase64String(stream.ToArray());
}

}
class DBProc
{

const string GET_SID = "WMSCONN";

protected string _connStr = string.Empty

public DBProc()
{
//以下自己填寫
string _dataSouce = "";
string _user ="";
string _pw="";
//這你自己填寫

_connStr = "Data Source=" + _dataSouce + ";Persist Security Info=True;User ID=" + _user + ";Password=" + _pw + ";Unicode=True";
}

public string GetConnectionstring()
{
return _connStr;
//ConnectionStringsSection _connectionStringsSection = WebConfigurationManager.GetSection("connectionStrings") as ConnectionStringsSection;
//return _connectionStringsSection.ConnectionStrings[GET_SID].ToString();
}

public DataTable OraExeFill(string cmdStr)
{
DataTable _returnTable = new DataTable();
OracleConnection _connection = new OracleConnection(GetConnectionstring());
try
{
OracleCommand _command = _connection.CreateCommand();
_command.CommandText = cmdStr;
OracleDataAdapter _adapter = new OracleDataAdapter(cmdStr, _connection);
_adapter.Fill(_returnTable);
}
catch (Exception)
{
}
return _returnTable;
}

public bool OraExeSP_UserAuth(string user, string pw, ref string msg)
{
bool _ret = false;
OracleConnection _connection = new OracleConnection(GetConnectionstring());
try
{
try
{
OracleCommand _command = _connection.CreateCommand();
_command.CommandType = CommandType.StoredProcere;
_command.CommandText = "TP.PKG_HR.SP_HR_USER_AUTH_1";
_command.Parameters.Add("P_USER", OracleType.VarChar, 20).Value = user;
_command.Parameters.Add("P_PW", OracleType.VarChar, 50).Value = pw;
_command.Parameters.Add("P_TYPE", OracleType.VarChar, 500).Direction = ParameterDirection.Output;
_command.Parameters.Add("P_RET", OracleType.Number, 1).Direction = ParameterDirection.Output;
_command.Parameters.Add("P_MSG", OracleType.VarChar, 500).Direction = ParameterDirection.Output;
_connection.Open();
_command.ExecuteNonQuery();
_ret = (_command.Parameters["P_RET"].Value != DBNull.Value) ? (Convert.ToInt32(_command.Parameters["P_RET"].Value) > 0) : false;
msg = (_command.Parameters["P_MSG"].Value != DBNull.Value) ? _command.Parameters["P_MSG"].Value.ToString() : "";
}
catch (Exception ex)
{
msg = ex.Message.ToString();
}
}
finally
{
_connection.Close();
}
return _ret;
}

public bool OraExeFill(string cmdStr,ref DataTable _dt,ref string _msg)
{
bool _ret=false;
OracleConnection _connection = new OracleConnection(GetConnectionstring());
try
{
OracleCommand _command = _connection.CreateCommand();
_command.CommandText = cmdStr;
OracleDataAdapter _adapter = new OracleDataAdapter(cmdStr, _connection);
_adapter.Fill(_dt);
_ret=true;
}
catch (Exception e)
{
_msg=e.Message;
}
return _ret;
}

public string OraExeScalar(string cmdStr, ref string msg)
{
string _scalarValue = "";
DataTable _returnTable = new DataTable();
OracleConnection _connection = new OracleConnection(GetConnectionstring());
try
{
_connection.Open();
OracleCommand _command = _connection.CreateCommand();
_command.CommandText = cmdStr;
_scalarValue = _command.ExecuteScalar().ToString();
}
catch (Exception ex)
{
msg = ex.Message.ToString();
return "";
}
finally
{
_connection.Close();
}
return _scalarValue;
}

public bool OraExeNonQuery(string cmdStr, ref string msg)
{
bool _ret = false;
OracleConnection _connection = new OracleConnection(GetConnectionstring());
try
{
_connection.Open();
OracleTransaction _trans = _connection.BeginTransaction();
OracleCommand _command = _connection.CreateCommand();
_command.Transaction = _trans;
try
{
_command.CommandText = cmdStr;
_command.ExecuteNonQuery();
_trans.Commit();
_ret = true;
}
catch (Exception ex1)
{
_trans.Rollback();
msg = ex1.Message.ToString();
}
}
catch (Exception ex)
{
msg = ex.Message.ToString();
}
finally
{
_connection.Close();
}
return _ret;
}

public bool OraExeNonQuery(ArrayList cmdStr, ref string msg)
{
bool _ret = false;
OracleConnection _connection = new OracleConnection(GetConnectionstring());
try
{
_connection.Open();
OracleTransaction _trans = _connection.BeginTransaction();
OracleCommand _command = _connection.CreateCommand();
_command.Transaction = _trans;
try
{
for (int i = 0; i < cmdStr.Count; i++)
{
_command.CommandText = cmdStr[i].ToString();
_command.ExecuteNonQuery();
}
_trans.Commit();
_ret = true;
}
catch (Exception ex1)
{
_trans.Rollback();
msg = ex1.Message.ToString();
}
}
catch (Exception ex)
{
msg = ex.Message.ToString();
}
finally
{
_connection.Close();
}
return _ret;
}
}
}

B. asp.net中資料庫連接的公共類的調用方法

下面的例子就是調用通用類的資料庫操作方法（資料庫的鏈接與關閉都在通用類中），不懂得花可以發例子給你。using System;
using System.Collections.Generic;
using System.Text;using TroubledTimes.Models;
using System.Data;
using System.Data.sqlClient;namespace TroubledTimes.DAL
{
/// <summary>
/// 官方活動信息數據訪問類
/// </灶兄summary>
public static class FunctionsService
{
/// <summary>
/// 1.根據不同情況查詢活動信息
/// </summary>
//敬模/ <param name="type">活動類型</param>
/// <param name="state">設置狀態</param>
/// <param name="name">活動名稱</param>
/// <param name="flag">控制變數</param>
/// <returns>活動信息對象的集合</returns>
public static IList<Functions> GetAllFunctions(string type,string state,string name,int flag)
{
string sql = "Select * from Functions where State =1";
if(type!="" && flag==1)
sql += " and FunState='" + type + "'";
else if (state != "" && flag == 2)
sql += " and SetState='" + state + "'";
else if (name!="" && flag==3)
sql += " and FunctionName like '%" + name + "%'";
else if (flag == 4)
sql += " and FunState='" + type + "隱稿襲' and SetState='" + state + "'";
else if (flag == 5)
sql += " and FunState='" + type + "' and FunctionName like '%" + name + "%'";
else if (flag == 6)
sql += " and SetState='" + state + "' and FunctionName like '%" + name + "%'";
else if (flag == 7)
sql += " and FunState='" + type + "' and SetState='" + state + "' and FunctionName like '%" + name + "%'";
sql += " order by FunNumber Desc";
IList<Functions> list = new List<Functions>();
try
{
// DataTable dt = DBHelper.GetScalar("up_SelectFunctions");
DataTable dt = DBHelper.GetDataTable(sql);
foreach (DataRow row in dt.Rows)
{
Functions function = new Functions();
function.FunctionName = (string)row["FunctionName"];
function.FId = (int)row["FId"];
function.FunctionUrl = (string)row["FunctionUrl"];
function.FunctionImg = (string)row["FunctionImg"];
function.FunctionContent = (string)row["FunctionContent"];
function.FunctionTime = (DateTime)row["FunctionTime"];
function.FunAdminUrl = (string)row["FunAdminUrl"];
function.FunState = (int)row["FunState"]; //--活動類型（游戲活動/官網活動,0:游戲）<後加>
function.SetState = (int)row["SetState"]; //--設置狀態（設置中/預設置,0:預設置）<後加>
function.FunNumber = (int)row["FunNumber"]; //--活動支持率（僅官網）<後加>
function.State = (int)row["State"]; //--存貯狀態（0/1）
list.Add(function);
}
return list;
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return null;
}
} /// <summary>
/// 2.根據活動類型獲取活動信息
/// </summary>
/// <param name="id">活動類型</param>
/// <returns>該活動類型的數量</returns>
public static int GetFunctionsByType(int type)
{
IList<Functions> list = new List<Functions>();
try
{
string sql = "select count(*) from Functions where SetState = 1 and FunState='" + type+ "'";

return DBHelper.Sanlar(sql); }
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return 0;
}
} /// <summary>
/// 3.根據活動ID修改活動信息
/// </summary>
/// <param name="f">活動信息類對象</param>
/// <returns>資料庫中受影響的行數</returns>
public static int ModifyFunctionsById(Functions f)
{
try
{
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@FId",f.FId),
new SqlParameter("@FunctionName",f.FunctionName),
new SqlParameter("@FunctionUrl",f.FunctionUrl),
new SqlParameter("@FunctionImg",f.FunctionImg),
new SqlParameter("@FunctionContent",f.FunctionContent),
new SqlParameter("@FunctionTime",f.FunctionTime),
new SqlParameter("@function.FunAdminUrl",f.FunAdminUrl),
new SqlParameter("@FunState",f.FunState),
new SqlParameter("@FunNumber",f.FunNumber),
new SqlParameter("@SetState",f.SetState),
new SqlParameter("@State",f.State)
};
return DBHelper.ExecuteProc("up_AmendFunctions", para);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return 0;
}
} /// <summary>
/// 4.添加活動信息
/// </summary>
/// <param name="f">活動信息類對象</param>
/// <returns>資料庫中受影響的行數</returns>
public static int AddFunctions(Functions f)
{
try
{
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@FunctionName",f.FunctionName),
new SqlParameter("@FunctionUrl",f.FunctionUrl),
new SqlParameter("@FunctionImg",f.FunctionImg),
new SqlParameter("@FunctionContent",f.FunctionContent),
new SqlParameter("@FunctionTime",f.FunctionTime),
new SqlParameter("@FunAdminUrl",f.FunAdminUrl),
new SqlParameter("@FunState",f.FunState),
new SqlParameter("@FunNumber",f.FunNumber),
new SqlParameter("@SetState",f.SetState),
new SqlParameter("@State",f.State) };
return DBHelper.ExecuteProc("up_AddFunctions", para);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return 0;
} }
/// <summary>
/// 5、根據id批量刪除活動信息(修改)
/// </summary>
/// <param name="id">活動信息id</param>
/// <returns>返回受影響的行數</returns>
public static int DeleteFunById(string ids)
{
//string sql = "update Functions set State = 0 where FId in('"+ids+"')";
string sql = "update Functions set State = 0 where FId ='" + ids + "'";
try
{
return DBHelper.ExecuteCommand(sql);
}
catch(Exception ex)
{
throw ex;
}
}
/// <summary>
/// 6、根據id修改設置狀態
/// </summary>
/// <param name="id">活動信息id</param>
/// <returns>返回受影響的行數</returns>
public static int UpdateSetById(int id, int setState)
{
string sql = "Update Functions set SetState = "+setState+" where FId="+id;
try
{
return DBHelper.ExecuteCommand(sql);
}
catch(Exception ex)
{
throw ex;
}
}
}
}

C. c# winform程序怎麼連接到本地sqlserver 資料庫啊

如需連接到本地SQL，請確保SQL服務正常啟動，然後執行如下代碼：

string con, sql;
/*Integrated Security=SSPI 這個表示以當前WINDOWS系統用戶身去登錄SQL SERVER伺服器，如果SQL SERVER伺服器不支持這種方式登錄時，就會出錯。
你可以使用SQL SERVER的用戶名和密碼進行登錄，如：
"Server=.;Database=YouDBName;User ID=sa;Password=密碼"
*/
con = "Server=.;Database=YouDBName;Integrated Security=SSPI";
sql = "select * from Test";
SqlConnection mycon = new SqlConnection(con);
mycon.Open();
SqlDataAdapter myda = new SqlDataAdapter(sql, con);
DataSet myds = new DataSet();
myda.Fill(myds, "Test");
dataGridView1.DataSource = myds.Tables["Test"];

D. 資料庫七種連接方式總結

部門表數據

內連接inner join表示A表和B表的共有部分數據。

左連接 left join 表示A表和B表的公共部分，再加上A表的獨有部分。

右連接right join 表示A表和B表公共部分，在加上B表的獨有部分。

查找A表獨有部分，則需查找A表和B表的共有部分並加上A表的獨有部分，在將A表和B表的共有部分剔除即可(也就是挑選B的主鍵為空的數據)。

查找B表獨有部分，則需查找A表和B表的共有部分並加上B表的獨有部分，在將A表和B表的共有部分剔除即可(也就是挑選A的主鍵為空的數據)。

全連接則表示將A表和B表的公共部分及A表、B表的獨有部分，所有數據都查詢出來

指導圖的全連接 full outer join 在mysql 語法報錯！但是可以通過union關鍵字進行查詢。
UNION會把 重復的行去掉，返回的行都是唯一的。如果想保留重復行，可以使用 UNION ALL 關鍵字。
UNION其實就是將A表和B表的共有部分及A表的獨有部分(即左連接left join)加上A、B表共有部分及B表的獨有部分(即右連接right join)合並起來，並進行去重即可。

查詢A表獨有部分並加上B表獨有部分

實際就是查詢A表的獨有部分和B表的獨有部分，使用UNION進行連接即可。