Session 相关使用 在MVC中的使用。。。
public class SecurityHelper
{
#region 1.0 使用 票据对象 将 用户数据 加密成字符串 +string EncryptUserInfo(string userInfo) /// <summary>
/// 使用 票据对象 将 用户数据 加密成字符串
/// </summary>
/// <param name="userInfo"></param>
/// <returns></returns>
public static string EncryptUserInfo(string userInfo)
{
//1.1 将用户数据 存入 票据对象
FormsAuthenticationTicket ticket = new FormsAuthenticationTicket(1, "aaa", DateTime.Now, DateTime.Now, true, userInfo);
//1.2 将票据对象 加密成字符串（可逆）
string strData = FormsAuthentication.Encrypt(ticket);
return strData;
} #endregion 1.0 使用 票据对象 将 用户数据 加密成字符串 +string EncryptUserInfo(string userInfo)
#region 2.0 加密字符串 解密 +string DecryptUserInfo(string cryptograph)
/// <summary>
/// 加密字符串 解密
/// </summary>
/// <param name="cryptograph">加密字符串</param>
/// <returns></returns>
public static string DecryptUserInfo(string cryptograph)
{
//1.1 将 加密字符串 解密成 票据对象
FormsAuthenticationTicket ticket = FormsAuthentication.Decrypt(cryptograph);
//1.2 将票据里的 用户数据 返回
return ticket.UserData;
} #endregion 2.0 加密字符串 解密 +string DecryptUserInfo(string cryptograph)
}
#region 将DataTable 转换成json数据
/// <summary>
/// 表转换成jason通用数据
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public string DataTableToJson(DataTable dt)
{
StringBuilder jsonBuilder = new StringBuilder();
jsonBuilder.Append("{\"");
jsonBuilder.Append("Result");
jsonBuilder.Append("\":");
jsonBuilder.Append("\"");
if (dt != null && dt.Rows.Count > 0)
{
jsonBuilder.Append("1");
jsonBuilder.Append("\",");
jsonBuilder.Append("\"");
jsonBuilder.Append("Table");
jsonBuilder.Append("\":[");
//jsonBuilder.Append("[");
for (int i = 0; i < dt.Rows.Count; i++)
{
jsonBuilder.Append("{");
for (int j = 0; j < dt.Columns.Count; j++)
{
jsonBuilder.Append("\"");
jsonBuilder.Append(dt.Columns[j].ColumnName);
jsonBuilder.Append("\":\"");
jsonBuilder.Append(dt.Rows[i][j].ToString());
jsonBuilder.Append("\",");
}
jsonBuilder.Remove(jsonBuilder.Length - 1, 1);
jsonBuilder.Append("},");
}
jsonBuilder.Remove(jsonBuilder.Length - 1, 1);
jsonBuilder.Append("]");
jsonBuilder.Append("}");
}
else
{
jsonBuilder.Append("0");
jsonBuilder.Append("\",");
jsonBuilder.Append("\"");
jsonBuilder.Append("Table");
jsonBuilder.Append("\":");
jsonBuilder.Append("\"");
jsonBuilder.Append("\"}");
}
return jsonBuilder.ToString();
}
#endregion #region 将键值对 转换成Json格式
/// <summary>
/// 将键值对 转换成Json格式
/// </summary>
/// <param name="argNames">请确保argNames与argValues都不为空，且元素数量一致</param>
/// <param name="argValues"></param>
/// <returns></returns>
public string ConvertToJsonByList(List<string> argNames, List<string> argValues)
{
/*
{
"Result": "1",
"Table": [
{
"OrderCost": "10.50",
"RenewCost": "5.00",
"TotalCost": "15.50"
}
]
}
*/
if (argNames == null || argValues == null || (argNames.Count != argValues.Count) || argNames.Count == 0)
{
return ConvertToJsonNoData();
} StringBuilder jsonBuilder = new StringBuilder();
jsonBuilder.Append("{\"");
jsonBuilder.Append("Result");
jsonBuilder.Append("\":");
jsonBuilder.Append("\""); jsonBuilder.Append("1");
jsonBuilder.Append("\",");
jsonBuilder.Append("\"");
jsonBuilder.Append("Table");
jsonBuilder.Append("\":[");
jsonBuilder.Append("{");
for (int i = 0; i < argNames.Count; i++)
{
jsonBuilder.Append("\"");
jsonBuilder.Append(argNames[i]);
jsonBuilder.Append("\":\"");
jsonBuilder.Append(argValues[i]);
jsonBuilder.Append("\",");
}
jsonBuilder.Remove(jsonBuilder.Length - 1, 1);
jsonBuilder.Append("}");
jsonBuilder.Append("]");
jsonBuilder.Append("}");
return jsonBuilder.ToString();
} #endregion
/*
[
{
"ID": "68",
"Remark": "3 "
},
{
"ID": "72",
"Remark": " "
}
]
*/
/// <summary>
/// 表转换成jason通用数据
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public string DataTableToJson_PureData(DataTable dt)
{
StringBuilder jsonBuilder = new StringBuilder();
//jsonBuilder.Append("{\"");
//jsonBuilder.Append("Result");
//jsonBuilder.Append("\":");
//jsonBuilder.Append("\"");
if (dt != null && dt.Rows.Count > 0)
{
//jsonBuilder.Append("1");
//jsonBuilder.Append("\",");
//jsonBuilder.Append("\"");
//jsonBuilder.Append("Table");
//jsonBuilder.Append("\":[");
jsonBuilder.Append("[");
for (int i = 0; i < dt.Rows.Count; i++)
{
jsonBuilder.Append("{");
for (int j = 0; j < dt.Columns.Count; j++)
{
jsonBuilder.Append("\"");
jsonBuilder.Append(dt.Columns[j].ColumnName);
jsonBuilder.Append("\":\"");
jsonBuilder.Append(dt.Rows[i][j].ToString());
jsonBuilder.Append("\",");
}
jsonBuilder.Remove(jsonBuilder.Length - 1, 1);
jsonBuilder.Append("},");
}
jsonBuilder.Remove(jsonBuilder.Length - 1, 1);
jsonBuilder.Append("]");
//jsonBuilder.Append("}");
}
else
{
jsonBuilder.Append("[]");
//jsonBuilder.Append("0");
//jsonBuilder.Append("\",");
//jsonBuilder.Append("\"");
//jsonBuilder.Append("Table");
//jsonBuilder.Append("\":");
//jsonBuilder.Append("\"");
//jsonBuilder.Append("\"}");
}
return jsonBuilder.ToString();
}
#region 返回 无数据的Json串 {"Result":"0","Table":""}
/// <summary>
/// 返回 无数据的Json串 {"Result":"0","Table":""}
/// </summary>
/// <returns></returns>
public static string ConvertToJsonNoData()
{
return "[]";
// return "{\"Result\":\"0\",\"Table\":\"\"}";
} #endregion
@{
ViewBag.Title = "通知管理";
Layout = "~/Views/Shared/_Layout.cshtml";
}@Scripts.Render("~/bundles/tools")
<script type="text/javascript">
function getNowFormatDate(addMonths, addDays) {
var t = new Date().addMonths(addMonths).addDays(addDays).format("yyyy-MM-dd hh:mm");
return t;
}
$(function () {
//$('#txtStartTime').datetimebox('setValue', getNowFormatDate(-1, 0));
//$('#txtEndTime').datetimebox('setValue', getNowFormatDate(0, 0)); var params = "?pageIndex=1&&pageSize=20&&sortName=&&ddlSendType=1&&txtReceivers=&&txtStartTime=&&txtEndTime=";
$('#dg').datagrid({
title: '',
//width: 750,
//height: 350,
nowrap: true,
autoRowHeight: true,
striped: true,
collapsible: false,
singleSelect: true,
pagination: true,
rownumbers: true,
loadMsg: '数据加载中,请稍后…',
url: '/DB/NOTICE_QRY' + params,
sortName: '',//'code',
sortOrder: '',//'desc',
remoteSort: false,
idField: 'code',
frozenColumns: [[
//{ field: 'ck', checkbox: true },
{ field: 'NOTICE_ID', title: '通知编号', width: 70, sortable: true }
]],
columns: [[ { field: 'NOTICE_TITLE', title: '标题', width: 120 },
{ field: 'NOTICE_CONTENT', title: '内容', width: 150 },
{
field: 'NOTICE_SENDTIME', title: '发送时间', width: 160, sortable: true,
sorter: function (a, b) {
return (a > b ? 1 : -1);
}
},
{
field: 'NOTICE_SENDTYPE', title: '发送类型', width: 150, sortable: true,
formatter: function (val, row, index) {
if (val == "1") { return "所有人"; }
else if (val == "2") { return "自定义"; } return val;
}
},
{ field: 'NOTICE_RECEIVERS', title: '接收者', width: 150 }
]],
toolbar: [
//,'-',
//{
// id: 'btnsave',
// text: '查询',
// disabled: false,
// iconCls: 'icon-search',
// handler: function () {
// //$('#btnsave').linkbutton('disable');
// alert('查询');
// }
//}
]
});
var p = $('#dg').datagrid('getPager');
$(p).pagination({
onBeforeRefresh: function () {
//alert('before refresh');
}
}); $("#btnSearch").click(function () {
var params = $("#searchBox1 form").serialize();
params += "&pageIndex=1&pageSize=20&&sortName="; var urlStr = '/DB/NOTICE_QRY?' + params;
$('#dg').datagrid('load', urlStr);
}); $("#btnExport").click(function () {
});
});
function checkedChange() {
if ($('input:radio[name="radioReceiver"]:checked').val() == 'ALL') {
$('#txtReceiver').attr("disabled", "disabled"); //.css('display', 'none')
} else {
$('#txtReceiver').removeAttr("disabled");
//$('#txtReceiver').css('display', 'block');
}
}
function sendNotice() { //var showmsg = $('#frmSendNotice').serialize();
//$.messager.alert('提示信息', showmsg, 'info'); $.ajax({
type: 'post',
url: '/DB/NOTICE_SEND',
data: $("#frmSendNotice").serialize(),
success: function (data) { var obj = JSON.parse(data);
if (obj.length > 0 && obj[0].Result == "1") {
var showmsg = "发送成功";
$.messager.alert('提示信息', showmsg, 'info'); //关闭窗口
$('#diagSendNotice').dialog('close'); //刷新网格
$('#dg').datagrid('reload');
}
else {
//发送失败
var showmsg = "发送失败";
$.messager.alert('提示信息', showmsg, 'info'); }
}
}); }
function onChangeDDLRec(newValue, oldValue) {
if (newValue == "1") {
$("#txtReceivers").css("display", "none");
$("#txtReceivers").attr("disabled", "disabled");
} else {
$("#txtReceivers").css("display", "block");
$("#txtReceivers").removeAttr("disabled");
$("#txtReceivers").focus();
}
}</script>
<div id="searchBox1">
<form>
<table>
<tr>
<td>
<span>接收者：</span>
<select id="ddlSendType" class="easyui-combobox" name="ddlSendType" style="width:100px;" data-options="editable:false,onChange:onChangeDDLRec">
<option value="1" selected>所有人</option>
<option value="2">自定义</option>
</select>
</td>
<td>
<input id="txtReceivers" name="txtReceivers" class="easyui-validatebox" data-options="required:false" disabled="disabled" placeholder="请输入接收者"
style="width:150px;display:none" />
</td>
<td>
<span>开始时间：</span>
<input class="easyui-datetimebox" id="txtStartTime" name="txtStartTime"
data-options="required:false,showSeconds:false" value="" style="width:130px">
</td>
<td>
<span>结束时间：</span>
<input class="easyui-datetimebox" id="txtEndTime" name="txtEndTime"
data-options="required:false,showSeconds:false" value="" style="width:130px">
</td>
<td style="margin:10px,10px,10px,50px">
<a id="btnSearch" href="#" class="easyui-linkbutton" data-options="iconCls:'icon-search'">查询</a>
</td>
<td style="margin:10px,10px,10px,10px">
<a id="btnExport" href="#" class="easyui-linkbutton" data-options="iconCls:'icon-search'">导出</a>
</td>
<td style="margin:10px,10px,10px,10px">
<a id="btnSendNotice" οnclick="$('#diagSendNotice').dialog('open').dialog('setTitle', '发送通知');"
href="#" class="easyui-linkbutton" data-options="iconCls:'icon-search'">发送通知</a>
</td>
</tr> </table>
</form>
</div>
<table id="dg"></table><div id="diagSendNotice" class="easyui-dialog" title="发送通知" style="width:500px;height:500px;" closed="true"
data-options="resizable:true,maximizable:true,modal:true
,buttons:[{
text:'发送',
handler:function(){
sendNotice();}
},{
text:'取消',
handler:function(){ $('#diagSendNotice').dialog('close');}
}]">
<form id="frmSendNotice">
<table>
<tr style="height:100px;">
<td>接收者：</td>
<td>
<table>
<tr>
<td>
<input id="radioReceiver1" type="radio" name="radioReceiver" value="ALL" checked="checked"
οnchange="checkedChange();" /><label for="radioReceiver1">所有人</label>
</td>
</tr>
<tr>
<td>
<input id="radioReceiver2" type="radio" name="radioReceiver" value="OTHER"
οnchange="checkedChange();" /><label for="radioReceiver2">自定义</label>
</td>
</tr>
</table>
</td>
<td>
<input name="txtReceiver" id="txtReceiver" style="width:215px;height:80px;text-align:left;vertical-align:top;" disabled="disabled" placeholder="多个请用英文逗号分隔">
</td>
</tr>
<tr>
<td> 标题：</td>
<td colspan="2">
<input name="txtTitle" id="txtTitle" class="easyui-validatebox" data-options="required:true" style="width:280px;float:left;">
</td>
</tr>
<tr>
<td> 内容：</td>
<td colspan="2">
<input name="txtContent" id="txtContent" class="easyui-validatebox" data-options="required:true" style="width:280px;height:220px;text-align:left;vertical-align:top;">
</td>
</tr>
</table>
</form> </div>
#region 消息
/// <summary>
/// 消息查询
/// </summary>
public ActionResult MSG_QRY(int pageIndex, int pageSize,
int ddlHandeFlag = 0, int ddlMsgType = 0, string txtUserId = "", string txtStartTime = "", string txtEndTime = "", string sortName = "")
{
string res = _srv.MSG_QRY(ddlHandeFlag, ddlMsgType, txtUserId, txtStartTime, txtEndTime, new PageInfo(pageIndex, pageSize, sortName)); return Content(res);
}
/// <summary>
/// 消息处理
/// </summary>
public ActionResult MSG_Handle(string msgId)
{
string res = _srv.MSG_Handle(msgId);
return Content(res);
}
#endregion
/// <summary>
/// easyui-datagrid json类
/// </summary>
public class EasyuiDatagridJson
{
public int total { get; set; }
public DataTable rows { get; set; }
public DataTable footer { get; set; }
}
public class PageInfo
{
public PageInfo(int argPageIndex, int argPageSize, string argSortName = "")
{
PageIndex = argPageIndex;
PageSize = argPageSize;
SortName = argSortName;
} /// <summary>
/// 返回值
/// </summary>
public int RowCount = 0; /// <summary>
/// 返回值(入参)
/// </summary>
public int PageIndex = 1; /// <summary>
/// 返回值(入参)
/// </summary>
public int PageSize = 10; /// <summary>
/// 返回值
/// </summary>
public int PageCount = 1; / <summary>
/ 提供一种父子结构的实现
/ </summary>
//public string ParentId = ""; /// <summary>
/// 排序列(入参)
/// </summary>
public string SortName = "";
} /// <summary>
/// 列表分页 数据结构
/// </summary>
public class PagedDataBase<T> where T : class
{
/// <summary>
/// 返回值
/// </summary>
public List<T> DataList = new List<T>(); /// <summary>
/// 返回值
/// </summary>
public int RowCount = 0; /// <summary>
/// 返回值(入参)
/// </summary>
public int PageIndex = 1; /// <summary>
/// 返回值(入参)
/// </summary>
public int PageSize = 10; /// <summary>
/// 返回值
/// </summary>
public int PageCount = 1; /// <summary>
/// 提供一种父子结构的实现
/// </summary>
public string ParentId = ""; /// <summary>
/// 排序列
/// </summary>
public string SortName = "";
} /// <summary>
/// 列表分页(附带返回其他参数的) 数据结构
/// </summary>
public class PagedDataBaseWithParams<T> : PagedDataBase<T> where T : class
{
/// <summary>
/// 返回的附带数据 Keys
/// </summary>
public List<string> Keys = new List<string>(); /// <summary>
/// 返回的附带数据 Values
/// </summary>
public List<string> Values = new List<string>();
}
showMsg.js :
//消息提示
function funcShowMsg(showMsg, showSeconds) {
if (showSeconds == undefined)
showSeconds = 3; $.messager.show({
title: '提示',
msg: showMsg,
showType: 'slide',
timeout: 1000 * showSeconds
});
}
public string MSG_QRY(int flag, int msgType, string userId, string startTime, string endTime, PageInfo argPageInfo)
{
try
{ #region sql语句
string sql = " SELECT * FROM MSG {0}";
#endregion #region where条件查询
string whereCondition = "";
if (flag == 1 || flag == 2)
{
whereCondition += " AND MSG_FLAG ='{0}' ";
whereCondition = string.Format(whereCondition, flag);
}
if (msgType == 1 || msgType == 2 || msgType == 3)
{
whereCondition += " AND MSG_TYPE ='{0}' ";
whereCondition = string.Format(whereCondition, msgType);
}
if (!string.IsNullOrEmpty(userId))
{
whereCondition += " AND MSG_USERID ='{0}' ";
whereCondition = string.Format(whereCondition, userId);
}
if (!string.IsNullOrEmpty(startTime))
{
whereCondition += " AND MSG_TIME >='{0}' ";
whereCondition = string.Format(whereCondition, startTime);
}
if (!string.IsNullOrEmpty(endTime))
{
whereCondition += " AND MSG_TIME <='{0}' ";
whereCondition = string.Format(whereCondition, endTime);
}
#endregion if (!string.IsNullOrEmpty(whereCondition.TrimStart().TrimEnd()))
{
whereCondition = " where " + whereCondition.Substring(whereCondition.IndexOf("AND", 0) + 3);
} sql = string.Format(sql, whereCondition);
//List<SqlParameter> para = new List<SqlParameter>();
//para.Add(new SqlParameter("@account",userId));
//DataSet dataSet = DbHelper.ExecuteDataset(DbHelper.GetConnection(), CommandType.Text, sql, para.ToArray()); DataSet dataSet = DbHelper.ExecuteDataset(DbHelper.GetConnection(), CommandType.Text, sql);
DataTable dt = dataSet.Tables[0];
return _common.DataTableToJson_PureData(dt);
}
catch (Exception ex)
{
} return Common.ConvertToJsonNoData();
}
/// <summary>
/// SqlServer数据访问帮助类
/// </summary>
public sealed class OracleHelper
{
#region 私有构造函数和方法 private OracleHelper() { }
/// <summary>
/// 将OracleParameter参数数组(参数值)分配给OracleCommand命令.
/// 这个方法将给任何一个参数分配DBNull.Value;
/// 该操作将阻止默认值的使用.
/// </summary>
/// <param name="command">命令名</param>
/// <param name="commandParameters">OracleParameters数组</param>
private static void AttachParameters(OracleCommand command, OracleParameter[] commandParameters)
{
if (command == null) throw new ArgumentNullException("command");
if (commandParameters != null)
{
foreach (OracleParameter p in commandParameters)
{
if (p != null)
{
// 检查未分配值的输出参数,将其分配以DBNull.Value.
if ((p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Input) &&
(p.Value == null))
{
p.Value = DBNull.Value;
}
command.Parameters.Add(p);
}
}
}
} /// <summary>
/// 预处理用户提供的命令,数据库连接/事务/命令类型/参数
/// </summary>
/// <param name="command">要处理的OracleCommand</param>
/// <param name="connection">数据库连接</param>
/// <param name="transaction">一个有效的事务或者是null值</param>
/// <param name="commandType">命令类型 (存储过程,命令文本, 其它.)</param>
/// <param name="commandText">存储过程名或都T-SQL命令文本</param>
/// <param name="commandParameters">和命令相关联的OracleParameter参数数组,如果没有参数为'null'</param>
/// <param name="mustCloseConnection"><c>true</c> 如果连接是打开的,则为true,其它情况下为false.</param>
private static void PrepareCommand(OracleCommand command, OracleConnection connection, OracleTransaction transaction, CommandType commandType, string commandText, OracleParameter[] commandParameters, out bool mustCloseConnection)
{
if (command == null) throw new ArgumentNullException("command");
if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText"); // If the provided connection is not open, we will open it
if (connection.State != ConnectionState.Open)
{
mustCloseConnection = true;
connection.Open();
}
else
{
mustCloseConnection = false;
} // 给命令分配一个数据库连接.
command.Connection = connection; // 设置命令文本(存储过程名或SQL语句)
command.CommandText = commandText; // 分配事务
if (transaction != null)
{
if (transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
command.Transaction = transaction;
} // 设置命令类型.
command.CommandType = commandType; // 分配命令参数
if (commandParameters != null)
{
AttachParameters(command, commandParameters);
}
return;
} #endregion 私有构造函数和方法结束
#region ExecuteNonQuery命令
/// <summary>
/// 执行指定数据库连接对象的命令
/// </summary>
/// <remarks>
/// 示例:
/// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new OracleParameter("@prodid", 24));
/// </remarks>
/// <param name="connection">一个有效的数据库连接对象</param>
/// <param name="commandType">命令类型(存储过程,命令文本或其它.)</param>
/// <param name="commandText">T存储过程名称或T-SQL语句</param>
/// <param name="commandParameters">SqlParamter参数数组</param>
/// <returns>返回影响的行数</returns>
public static int ExecuteNonQuery(OracleConnection connection, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
{
if (commandText.Length <= 50)
Log.Info("调用存储过程：" + commandText);
if (commandParameters != null)
foreach (OracleParameter item in commandParameters)
Log.Info(item.ParameterName + " " + item.Value ?? ""); if (connection == null) throw new ArgumentNullException("connection");
// 创建OracleCommand命令,并进行预处理
OracleCommand cmd = new OracleCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, connection, (OracleTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection); // Finally, execute the command
int retval = cmd.ExecuteNonQuery(); // 清除参数,以便再次使用.
cmd.Parameters.Clear();
if (mustCloseConnection)
connection.Close();
return retval;
} /// <summary>
/// 执行带事务的OracleCommand(指定参数).
/// </summary>
/// <remarks>
/// 示例:
/// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "GetOrders", new OracleParameter("@prodid", 24));
/// </remarks>
/// <param name="transaction">一个有效的数据库连接对象</param>
/// <param name="commandType">命令类型(存储过程,命令文本或其它.)</param>
/// <param name="commandText">存储过程名称或T-SQL语句</param>
/// <param name="commandParameters">SqlParamter参数数组</param>
/// <returns>返回影响的行数</returns>
public static int ExecuteNonQuery(OracleTransaction transaction, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); // 预处理
OracleCommand cmd = new OracleCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection); // 执行
int retval = cmd.ExecuteNonQuery(); // 清除参数集,以便再次使用.
cmd.Parameters.Clear();
return retval;
} #endregion ExecuteNonQuery方法结束
#region ExecuteDataset方法
/// <summary>
/// 执行指定数据库连接对象的命令,返回DataSet.
/// </summary>
/// <remarks>
/// 示例:
/// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders");
/// </remarks>
/// <param name="connection">一个有效的数据库连接对象</param>
/// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>
/// <param name="commandText">存储过程名或T-SQL语句</param>
/// <returns>返回一个包含结果集的DataSet</returns>
public static DataSet ExecuteDataset(OracleConnection connection, CommandType commandType, string commandText)
{
return ExecuteDataset(connection, commandType, commandText, (OracleParameter[])null);
} /// <summary>
/// 执行指定数据库连接对象的命令,指定存储过程参数,返回DataSet.
/// </summary>
/// <remarks>
/// 示例:
/// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new OracleParameter("@prodid", 24));
/// </remarks>
/// <param name="connection">一个有效的数据库连接对象</param>
/// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>
/// <param name="commandText">存储过程名或T-SQL语句</param>
/// <param name="commandParameters">SqlParamter参数数组</param>
/// <returns>返回一个包含结果集的DataSet</returns>
public static DataSet ExecuteDataset(OracleConnection connection, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
{
if (connection == null) throw new ArgumentNullException("connection");
if (commandText.Length <= 50)
Log.Info("调用存储过程：" + commandText);
if (commandParameters != null)
foreach (OracleParameter item in commandParameters)
Log.Info(item.ParameterName + " " + item.Value ?? ""); if (connection == null) throw new ArgumentNullException("connection");
// 预处理
OracleCommand cmd = new OracleCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, connection, (OracleTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection); // 创建OracleDataAdapter和DataSet.
using (OracleDataAdapter da = new OracleDataAdapter(cmd))
{
DataSet ds = new DataSet(); // 填充DataSet.
da.Fill(ds); cmd.Parameters.Clear();
if (mustCloseConnection)
connection.Close(); return ds;
}
}
#endregion ExecuteDataset数据集命令结束
}
public static class DbHelper
{
public static bool IsOracle
{
get
{
return AppConfig.Get("ConStr").Contains("PORT=1521");
}
} #region 数据库连接
/// <summary>
/// 一个有效的数据库连接字符串
/// </summary>
/// <returns></returns>
public static string GetConnSting()
{
return AppConfig.Get("ConStr");
}
/// <summary>
/// 一个有效的数据库连接对象
/// </summary>
/// <returns></returns>
public static DbConnection GetConnection()
{
DbConnection Connection = null;
if (IsOracle)
Connection = new OracleConnection(DbHelper.GetConnSting());
else
Connection = new SqlConnection(DbHelper.GetConnSting());
return Connection;
} private static List<OracleParameter> Convert2OraPara(SqlParameter[] commandParameters)
{
List<OracleParameter> para = new List<OracleParameter>(); foreach (var sqlPara in commandParameters)
{
OracleParameter oraPara = new OracleParameter();
switch (sqlPara.DbType)
{
case DbType.Int32:
oraPara.OracleDbType = OracleDbType.Int32;
break;
case DbType.String:
oraPara.OracleDbType = OracleDbType.Varchar2;
break;
case DbType.StringFixedLength:
oraPara.OracleDbType = OracleDbType.NVarchar2;
break;
case DbType.Decimal:
oraPara.OracleDbType = OracleDbType.Decimal;
break;
} oraPara.Direction = sqlPara.Direction;
oraPara.ParameterName = sqlPara.ParameterName.Replace("@", "v_");
oraPara.Value = sqlPara.Value;
if (oraPara.ParameterName == "TABLE_RESULT")
oraPara.OracleDbType = OracleDbType.RefCursor;
para.Add(oraPara);
}
return para;
} /// <summary>
/// 给原参数类型为Output的赋值
/// </summary>
private static void Convert2SqlPara(OracleParameter[] commandParameters, SqlParameter[] sqlCommandParametersForOutput)
{
foreach (var oraPara in commandParameters)
{
if (oraPara.Direction != ParameterDirection.Output) continue; var par = sqlCommandParametersForOutput.Where(a => a.ParameterName.Replace("@", "v_") == oraPara.ParameterName)
.FirstOrDefault();
if (par != null)
{
switch (oraPara.OracleDbType)
{
case OracleDbType.Int32:
par.Value = Convert.ToInt32(oraPara.Value.ToString());
break;
case OracleDbType.Varchar2:
case OracleDbType.NVarchar2:
par.Value = oraPara.Value.ToString();
break;
case OracleDbType.Decimal:
par.Value = Convert.ToDecimal(oraPara.Value.ToString());
break;
}
}
}
} public static string HandBlockText(string strContent, string strBegin, string strEnd)
{
int IndexBegin = 0;
int IndexEnd = 0;
int nStart = 0;
string strSource = String.Empty;
do
{
nStart = Math.Min(IndexEnd, strContent.Length);
IndexBegin = strContent.IndexOf(strBegin,Math.Max(0,nStart-1)); if (IndexBegin == -1)
return strContent; IndexEnd = strContent.IndexOf(strEnd, IndexBegin);
if (IndexEnd == -1)
return strContent; strSource = strContent.Substring(IndexBegin, IndexEnd - IndexBegin + strEnd.Length);
strContent = strContent.Replace(strSource, GetReplaceAs(strSource));
}
while (strContent.IndexOf(strBegin, Math.Min(IndexEnd-1, strContent.Length)) > 0 && strContent.IndexOf(strEnd, Math.Min(IndexEnd-1, strContent.Length)) > 0); return strContent;
} private static string GetReplaceAs(string strSource)
{
int index = strSource.IndexOf(" ", strSource.IndexOf(" ") + 1);
strSource = strSource.Insert(index, "(");
index = strSource.IndexOf(";");
strSource = strSource.Insert(index, ")");
//处理=》
strSource = strSource.Replace("=", "=>");
strSource = strSource.Replace("EXEC ", ""); return strSource;
} public static int ExecuteNonQuery(DbConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
if (!IsOracle)
return SqlHelper.ExecuteNonQuery((connection as SqlConnection), commandType, commandText, commandParameters); List<OracleParameter> oraPramList = Convert2OraPara(commandParameters);
int result = OracleHelper.ExecuteNonQuery((connection as OracleConnection), commandType, commandText, oraPramList.ToArray()); //给原参数类型为Output的赋值
Convert2SqlPara(oraPramList.ToArray(), commandParameters); return result;
} public static DataSet ExecuteDataset(DbConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
try
{
if (!IsOracle)
return SqlHelper.ExecuteDataset((connection as SqlConnection), commandType, commandText, commandParameters);
else
{
StackTrace ss = new StackTrace(true);
//index:0为本身的方法；1为调用方法；2为其上上层，依次类推
MethodInfo mb = ss.GetFrame(1).GetMethod() as MethodInfo;
if (mb.ReturnType.Name == "DataTable")
{
List<SqlParameter> para = commandParameters.ToList();
para.Add(new SqlParameter("TABLE_RESULT", "0"));
para.LastOrDefault().Direction = ParameterDirection.Output;
commandParameters = para.ToArray();
}
return OracleHelper.ExecuteDataset((connection as OracleConnection), commandType, commandText, Convert2OraPara(commandParameters).ToArray());
}
}
catch (Exception ex)
{
throw ex;
}
} #endregion
}
public static class DbHelper
{
//public static bool IsOracle
//{
// get
// {
// return AppConfig.Get("ConStr").Contains("PORT=1521");
// }
//} #region 连接字符串
/// <summary>
/// 一个有效的数据库连接字符串
/// </summary>
/// <returns></returns>
public static string GetConnSting()
{
return ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString;
//return connString;
}
/// <summary>
/// 一个有效的数据库连接对象
/// </summary>
/// <returns></returns>
public static DbConnection GetConnection()
{
DbConnection Connection = null;
//if (IsOracle)
// Connection = new OracleConnection(DbHelper.GetConnSting());
//else
Connection = new SqlConnection(DbHelper.GetConnSting());
return Connection;
} #endregion
/ <summary>
/ 一个有效的数据库连接字符串
/ </summary>
/ <returns></returns>
//public static string GetConnSting()
//{
// return AppConfig.Get("ConStr");
//}
/ <summary>
/ 一个有效的数据库连接对象
/ </summary>
/ <returns></returns>
//public static DbConnection GetConnection()
//{
// DbConnection Connection = null;
// if (IsOracle)
// Connection = new OracleConnection(DbHelper.GetConnSting());
// else
// Connection = new SqlConnection(DbHelper.GetConnSting());
// return Connection;
//} //private static List<OracleParameter> Convert2OraPara(SqlParameter[] commandParameters)
//{
// List<OracleParameter> para = new List<OracleParameter>(); // foreach (var sqlPara in commandParameters)
// {
// OracleParameter oraPara = new OracleParameter();
// switch (sqlPara.DbType)
// {
// case DbType.Int32:
// oraPara.OracleDbType = OracleDbType.Int32;
// break;
// case DbType.String:
// oraPara.OracleDbType = OracleDbType.Varchar2;
// break;
// case DbType.StringFixedLength:
// oraPara.OracleDbType = OracleDbType.NVarchar2;
// break;
// case DbType.Decimal:
// oraPara.OracleDbType = OracleDbType.Decimal;
// break;
// } // oraPara.Direction = sqlPara.Direction;
// oraPara.ParameterName = sqlPara.ParameterName.Replace("@", "v_");
// oraPara.Value = sqlPara.Value;
// if (oraPara.ParameterName == "TABLE_RESULT")
// oraPara.OracleDbType = OracleDbType.RefCursor;
// para.Add(oraPara);
// }
// return para;
//} / <summary>
/ 给原参数类型为Output的赋值
/ </summary>
//private static void Convert2SqlPara(OracleParameter[] commandParameters, SqlParameter[] sqlCommandParametersForOutput)
//{
// foreach (var oraPara in commandParameters)
// {
// if (oraPara.Direction != ParameterDirection.Output) continue; // var par = sqlCommandParametersForOutput.Where(a => a.ParameterName.Replace("@", "v_") == oraPara.ParameterName)
// .FirstOrDefault();
// if (par != null)
// {
// switch (oraPara.OracleDbType)
// {
// case OracleDbType.Int32:
// par.Value = Convert.ToInt32(oraPara.Value.ToString());
// break;
// case OracleDbType.Varchar2:
// case OracleDbType.NVarchar2:
// par.Value = oraPara.Value.ToString();
// break;
// case OracleDbType.Decimal:
// par.Value = Convert.ToDecimal(oraPara.Value.ToString());
// break;
// }
// }
// }
//} //public static string HandBlockText(string strContent, string strBegin, string strEnd)
//{
// int IndexBegin = 0;
// int IndexEnd = 0;
// int nStart = 0;
// string strSource = String.Empty;
// do
// {
// nStart = Math.Min(IndexEnd, strContent.Length);
// IndexBegin = strContent.IndexOf(strBegin,Math.Max(0,nStart-1)); // if (IndexBegin == -1)
// return strContent; // IndexEnd = strContent.IndexOf(strEnd, IndexBegin);
// if (IndexEnd == -1)
// return strContent; // strSource = strContent.Substring(IndexBegin, IndexEnd - IndexBegin + strEnd.Length);
// strContent = strContent.Replace(strSource, GetReplaceAs(strSource));
// }
// while (strContent.IndexOf(strBegin, Math.Min(IndexEnd-1, strContent.Length)) > 0 && strContent.IndexOf(strEnd, Math.Min(IndexEnd-1, strContent.Length)) > 0); // return strContent;
//} //private static string GetReplaceAs(string strSource)
//{
// int index = strSource.IndexOf(" ", strSource.IndexOf(" ") + 1);
// strSource = strSource.Insert(index, "(");
// index = strSource.IndexOf(";");
// strSource = strSource.Insert(index, ")");
// //处理=》
// strSource = strSource.Replace("=", "=>");
// strSource = strSource.Replace("EXEC ", ""); // return strSource;
//} public static int ExecuteNonQuery(DbConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
// if (!IsOracle)
return SqlHelper.ExecuteNonQuery((connection as SqlConnection), commandType, commandText, commandParameters); //List<OracleParameter> oraPramList = Convert2OraPara(commandParameters);
//int result = OracleHelper.ExecuteNonQuery((connection as OracleConnection), commandType, commandText, oraPramList.ToArray()); 给原参数类型为Output的赋值
//Convert2SqlPara(oraPramList.ToArray(), commandParameters); //return result;
} public static DataSet ExecuteDataset(DbConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
try
{
//if (!IsOracle)
return SqlHelper.ExecuteDataset((connection as SqlConnection), commandType, commandText, commandParameters);
//else
//{
// StackTrace ss = new StackTrace(true);
// //index:0为本身的方法；1为调用方法；2为其上上层，依次类推
// MethodInfo mb = ss.GetFrame(1).GetMethod() as MethodInfo;
// if (mb.ReturnType.Name == "DataTable")
// {
// List<SqlParameter> para = commandParameters.ToList();
// para.Add(new SqlParameter("TABLE_RESULT", "0"));
// para.LastOrDefault().Direction = ParameterDirection.Output;
// commandParameters = para.ToArray();
// }
// return OracleHelper.ExecuteDataset((connection as OracleConnection), commandType, commandText, Convert2OraPara(commandParameters).ToArray());
//}
}
catch (Exception ex)
{
throw ex;
}
}
-----------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
