using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using GIS.Domain;
namespace GIS.DAO
{
/// <summary>
/// 获取数据库字符串
/// </summary>
public abstract class DBHelper
{
public SqlConnection Getconn()
{
string Strconn = ConfigurationManager.AppSettings["conn"].ToString();
SqlConnection conn = new SqlConnection(Strconn);
return conn;
}
public SqlConnection Getconns()
{
string Strconn = ConfigurationManager.AppSettings["conns"].ToString();
SqlConnection conn = new SqlConnection(Strconn);
return conn;
}
}
/// <summary>
/// 数据操作类
/// </summary>
public class SQLHelper : DBHelper
{
/// <summary>
/// 执行SQL语句
/// </summary>
/// <param name="sql">SQL语句(UPDATE、INSERT、DELETE)</param>
/// <returns>返回受影响行数</returns>
public int RunSQL(string sql, params SqlParameter[] parameters)
{
SqlConnection conn = null;
SqlCommand cmd = null;
int count = 0;
try
{
conn = Getconn();
conn.Open();
cmd = new SqlCommand(sql, conn);
cmd.Parameters.Clear();
if(parameters != null && parameters.Length > 0)
cmd.Parameters.AddRange(parameters);
count = cmd.ExecuteNonQuery();
}
catch(Exception e)
{
ExceptionText.SaveText(e.ToString() + sql);
}
finally
{
conn.Close();
}
return count;
}
/// <summary>
/// 返回首行首列
/// </summary>
/// <param name="sql"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public int ReturnSQL(string sql, params SqlParameter[] parameters)
{
SqlConnection conn = null;
SqlCommand cmd = null;
int count = 0;
try
{
conn = Getconn();
conn.Open();
cmd = new SqlCommand(sql, conn);
cmd.Parameters.Clear();
if(parameters != null && parameters.Length > 0)
cmd.Parameters.AddRange(parameters);
count = Convert.ToInt32(cmd.ExecuteScalar());
}
catch(Exception e)
{
ExceptionText.SaveText(e.ToString() + sql);
}
finally
{
conn.Close();
}
return count;
}
public SqlDataReader ReadSQL(string sql, params SqlParameter[] parameters)
{
SqlConnection conn = null;
SqlCommand cmd = null;
SqlDataReader reader = null;
try
{
conn = Getconn();
conn.Open();
cmd = new SqlCommand(sql, conn);
cmd.Parameters.Clear();
if(parameters != null && parameters.Length > 0)
cmd.Parameters.AddRange(parameters);
reader = cmd.ExecuteReader();
}
catch(Exception e)
{
ExceptionText.SaveText(e.ToString());
}
return reader;
}
//返回DataSet
public DataSet GetDataSet(string sql, params SqlParameter[] parameters)
{
SqlConnection conn = null;
SqlCommand cmd = null;
SqlDataAdapter sda = null;
DataSet ds = null;
try
{
conn = Getconn();
conn.Open();
cmd = new SqlCommand(sql, conn);
cmd.Parameters.Clear();
if(parameters != null && parameters.Length > 0)
cmd.Parameters.AddRange(parameters);
sda = new SqlDataAdapter();
sda.SelectCommand = cmd;
sda.SelectCommand.CommandTimeout = 1200;
ds = new DataSet();
sda.Fill(ds);
}
catch(Exception e)
{
ExceptionText.SaveText(e.ToString() + sql);
}
finally
{
conn.Close();
}
return ds;
}
public DataSet GetDataSets(string sql, params SqlParameter[] parameters)
{
SqlConnection conn = null;
SqlCommand cmd = null;
SqlDataAdapter sda = null;
DataSet ds = null;
try
{
conn = Getconns();
conn.Open();
cmd = new SqlCommand(sql, conn);
cmd.Parameters.Clear();
if(parameters != null && parameters.Length > 0)
cmd.Parameters.AddRange(parameters);
sda = new SqlDataAdapter();
sda.SelectCommand = cmd;
ds = new DataSet();
sda.Fill(ds);
}
catch(Exception e)
{
ExceptionText.SaveText(e.ToString());
}
finally
{
conn.Close();
}
return ds;
}
/// <summary>
/// 增删改操作 使用存储过程
/// </summary>
/// <param name="procName"></param>
/// <param name="sp"></param>
/// <returns></returns>
public int RunProc(string procName, params SqlParameter[] sp)
{
int count = 0;
SqlConnection conn = null;
SqlCommand cmd = null;
try
{
conn = Getconn();
conn.Open();
cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = procName;
cmd.Connection = conn;
cmd.Parameters.Clear();
if(sp != null && sp.Length > 0)
cmd.Parameters.AddRange(sp);
count = cmd.ExecuteNonQuery();
}
catch(Exception e)
{
ExceptionText.SaveText(e.ToString());
}
finally
{
conn.Close();
}
return count;
}
/// <summary>
/// 查询操作 使用存储过程
/// </summary>
/// <param name="procName">存储过程名称</param>
/// <param name="sp">SqlParameter</param>
/// <returns>DataSet</returns>
public DataSet GetProcDataSet(string procName, params SqlParameter[] sp)
{
SqlConnection conn = null;
SqlCommand cmd;
SqlDataAdapter sda;
DataSet ds = null;
try
{
conn = Getconn();
conn.Open();
cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = procName;
cmd.Connection = conn;
cmd.Parameters.Clear();
if(sp != null && sp.Length > 0)
cmd.Parameters.AddRange(sp);
sda = new SqlDataAdapter();
sda.SelectCommand = cmd;
ds = new DataSet();
sda.Fill(ds);
}
catch(Exception e)
{
ExceptionText.SaveText(e.ToString());
}
finally
{
conn.Close();
}
return ds;
}
/// <summary>
/// 带输出参数存储过程
/// </summary>
/// <param name="procName">存储过程名称</param>
/// <param name="sp">SqlParameter[]</param>
/// <returns></returns>
public string OutPutProc(string procName, params SqlParameter[] sp)
{
string allmoney = string.Empty;
SqlConnection conn = null;
SqlCommand cmd;
try
{
conn = Getconn();
conn.Open();
cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = procName;
cmd.Connection = conn;
cmd.Parameters.Clear();
if(sp != null && sp.Length > 0)
cmd.Parameters.AddRange(sp);
cmd.ExecuteNonQuery();
if(sp != null && sp.Length > 0)
{
for(int i = 0; i < sp.Length; i++)
{
if(sp[i].Direction == ParameterDirection.Output)
{
allmoney = Convert.ToString(sp[i].Value);
}
}
}
}
catch(Exception e)
{
ExceptionText.SaveText(e.ToString());
}
finally
{
conn.Close();
}
return allmoney;
}
/// 执行多条SQL语句，实现数据库事务。
///
///多条SQL语句
///影响的记录数
public int ExecuteSqlTran(List<String> SQLStringList)
{
using(SqlConnection conn = Getconn())
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
SqlTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
int count = 0;
for(int n = 0; n < SQLStringList.Count; n++)
{
string strsql = SQLStringList[n];
if(strsql.Trim().Length > 1)
{
cmd.CommandText = strsql;
count += cmd.ExecuteNonQuery();
}
}
tx.Commit();
return count;
}
catch
{
tx.Rollback();
return 0;
}
}
}
}
}
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using GIS.IDAO;
using GIS.Domain;
using System.Data.SqlClient;
namespace GIS.DAO
{
/// <summary>
/// 保险套餐数据访问层
/// </summary>
public class InsurancePackageDAO : IInsurancePackageDAO
{
SQLHelper sqlHelper = new SQLHelper();
/// <summary>
/// 通过条件获取保险套餐
/// </summary>
/// <param name="start"></param>
/// <param name="limit"></param>
/// <param name="condition">sqlWhere条件</param>
/// <returns></returns>
public DataSet GetInsurancePackageDao(int start, int limit, string condition)
{
int startIndex = start + 1;
int endIndex = limit + start;
string sql = string.Format(@"SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY ID) AS RowNumber,* FROM dbo.VT_InsurancePackage Where 1=1 {2}) AS t
WHERE (RowNumber BETWEEN {0} AND {1} )", startIndex, endIndex, condition);
return sqlHelper.GetDataSet(sql);
}
public int GetInsurancePackageTotal(string condition)
{
string sql = string.Format(@"SELECT COUNT(1) FROM VT_InsurancePackage WHERE 1=1 {0}", condition);
return sqlHelper.ReturnSQL(sql);
}
/// <summary>
/// 删除保险套餐
/// </summary>
/// <param name="Id"></param>
/// <returns></returns>
public int DelInsurancePackageDao(string Ids)
{
string sql = string.Format(@"Delete Vt_InsurancePackage Where ID in ({0})", Ids);
return sqlHelper.RunSQL(sql);
}
/// <summary>
/// 添加保险套餐
/// </summary>
/// <param name="insurancePackage">insurancePackage对象</param>
/// <returns></returns>
public int AddInsurancePackage(InsurancePackage insurancePackage)
{
SqlParameter[] sps = new SqlParameter[] {
new SqlParameter("@PackageName",insurancePackage.PackageName),
new SqlParameter("@Price",insurancePackage.Price),
new SqlParameter("@Number",insurancePackage.Number),
new SqlParameter("@OutDate", insurancePackage.OutDate),
new SqlParameter("@Remark",insurancePackage.Remark),
new SqlParameter("@PackageType",insurancePackage.PackageType),
new SqlParameter("@MenberlevelId",insurancePackage.discounttype.MenberlevelId),
new SqlParameter("@DiscountType",insurancePackage.discounttype.DiscountType)};
using(DataSet ds = sqlHelper.GetProcDataSet("P_AddInsurancePackage", sps))
{
int id = 0;
if(ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
id = string.IsNullOrEmpty(ds.Tables[0].Rows[0][0].ToString()) ? 0 : int.Parse(ds.Tables[0].Rows[0][0].ToString());
}
return id;
}
}
/// <summary>
/// 更改保险套餐和打折率
/// </summary>
/// <param name="insurancePackage"></param>
/// <returns></returns>
public int UpdateInsurancePackage(InsurancePackage insurancePackage)
{
string sql = string.Format(@"EXEC P_UpdateInsurancePackage
@PackageId = {0},
@PackageName = '{1}',
@Price = {2},
@Number = {3},
@OutDate = '{4}',
@Remark = '{5}',
@DiscountID ='{6}',
@DiscountType = '{7}'",
insurancePackage.ID,
insurancePackage.PackageName,
insurancePackage.Price,
insurancePackage.Number,
insurancePackage.OutDate,
insurancePackage.Remark,
insurancePackage.discounttype.DiscountID,
insurancePackage.discounttype.DiscountType);
return sqlHelper.RunSQL(sql);
}
}
}
/****** Object: StoredProcedure [dbo].[P_AddInsurancePackage] Script Date: 03/19/2014 20:07:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create 2014/02/13>
-- Description: 保存保险套餐
-- =============================================
ALTER PROCEDURE [dbo].[P_AddInsurancePackage]
@PackageName VARCHAR(50),
@Price DECIMAL(10,2),
@Number INT,
@OutDate DATETIME,
@Remark VARCHAR(225),
@PackageType INT,
@MenberlevelId VARCHAR(1000),
@DiscountType VARCHAR(1000)
AS
BEGIN
--添加保险套餐
INSERT INTO Vt_InsurancePackage
( PackageName ,
Price ,
Number ,
OutDate ,
Remark ,
PackageType
)
VALUES ( @PackageName , -- PackageName - varchar(50)
@Price , -- Price - decimal
@Number , -- Number - int
@OutDate , -- OutDate - datetime
@Remark , -- Remark - varchar(225)
@PackageType
)
DECLARE @PackageID INT
SET @PackageID = @@IDENTITY
IF(@MenberlevelId<>'' AND @DiscountType<>'')
BEGIN
--保存到打折率表
DECLARE Temp1_Cursor CURSOR FOR SELECT Value FROM dbo.SplitString(@MenberlevelId, ',', 1)
DECLARE Temp2_Cursor CURSOR FOR SELECT Value FROM dbo.SplitString(@DiscountType, ',', 1)
OPEN Temp1_Cursor
OPEN Temp2_Cursor
DECLARE @Temp1Value NVARCHAR(MAX)
DECLARE @Temp2Value NVARCHAR(MAX)
FETCH NEXT FROM Temp1_Cursor INTO @Temp1Value
FETCH NEXT FROM Temp2_Cursor INTO @Temp2Value
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO VT_DiscountType
( InsurancePackageID ,
MenberlevelId ,
DiscountType
)
VALUES ( @PackageID , -- InsurancePackageID - int
@Temp1Value , -- MenberlevelId - varchar(100)
@Temp2Value -- DiscountType - decimal
)
--PRINT @Temp1Value
--PRINT @Temp2Value
--PRINT '-----------------------------------'
FETCH NEXT FROM Temp1_Cursor INTO @Temp1Value
FETCH NEXT FROM Temp2_Cursor INTO @Temp2Value
END
CLOSE Temp1_Cursor
DEALLOCATE Temp1_Cursor
CLOSE Temp2_Cursor
DEALLOCATE Temp2_Cursor
END
SELECT @PackageID
END
/****** Object: UserDefinedFunction [dbo].[SplitString] Script Date: 03/19/2014 20:09:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[SplitString]
(
@Input nvarchar(max) , --input string to be separated
@Separator nvarchar(max) = ',' , --a string that delimit the substrings in the input string
@RemoveEmptyEntries bit = 1 --the return value does not include array elements that contain an empty string
)
RETURNS @TABLE TABLE
(
[Id] int IDENTITY(1, 1) ,
[Value] nvarchar(max)
)
AS
BEGIN
DECLARE
@Index int ,
@Entry nvarchar(max)
SET @Index = CHARINDEX(@Separator, @Input)
WHILE ( @Index > 0 )
BEGIN
SET @Entry = LTRIM(RTRIM(SUBSTRING(@Input, 1, @Index - 1)))
IF ( @RemoveEmptyEntries = 0 )
OR ( @RemoveEmptyEntries = 1
AND @Entry <> ''
)
BEGIN
INSERT INTO @TABLE
( [Value] )
VALUES
( @Entry )
END
SET @Input = SUBSTRING(@Input,
@Index + DATALENGTH(@Separator) / 2,
LEN(@Input))
SET @Index = CHARINDEX(@Separator, @Input)
END
SET @Entry = LTRIM(RTRIM(@Input))
IF ( @RemoveEmptyEntries = 0 )
OR ( @RemoveEmptyEntries = 1
AND @Entry <> ''
)
BEGIN
INSERT INTO @TABLE
( [Value] )
VALUES
( @Entry )
END
RETURN
END
