连接数据库公共类
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进行连接即可。