#region 数据库操作类的封装 public abstract class SqlHelper { //Database connection strings public static readonly string ConnectionStringLocalTransaction = Connstr; // Hashtable to store cached parameters private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable()); /*返回数据库连接*/ public static SqlConnection getConnection() { SqlConnection c = new SqlConnection(ConnectionStringLocalTransaction); return c; } /// <summary> /// Execute a SqlCommand that returns a resultset against the database specified in the connection string /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// SqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param>a valid connection string for a SqlConnection</param> /// <param>the CommandType (stored procedure, text, etc.)</param> /// <param>the stored procedure name or T-SQL command</param> /// <param>an array of SqlParamters used to execute the command</param> /// <returns>A SqlDataReader containing the results</returns> public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); SqlConnection conn = new SqlConnection(connectionString); // we use a try/catch here because if the method throws an exception we want to // close the connection throw code, because no datareader will exist, hence the // commandBehaviour.CloseConnection will not work try { PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); cmd.Parameters.Clear(); return rdr; } catch { conn.Close(); throw; } } public static DataSet ReturnDataSet(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); SqlConnection conn = new SqlConnection(ConnectionStringLocalTransaction); // we use a try/catch here because if the method throws an exception we want to // close the connection throw code, because no datareader will exist, hence the // commandBehaviour.CloseConnection will not work try { PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); DataSet ds = new DataSet(); SqlDataAdapter sda = new SqlDataAdapter(cmd); sda.Fill(ds); return ds; } catch { conn.Close(); throw; } } /// <summary> /// Execute a SqlCommand that returns a resultset against the database specified in the connection string /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// SqlDataReader r = ExecuteReader(SqlConnection, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param>a valid connection string for a SqlConnection</param> /// <param>the CommandType (stored procedure, text, etc.)</param> /// <param>the stored procedure name or T-SQL command</param> /// <param>an array of SqlParamters used to execute the command</param> /// <returns>A SqlDataReader containing the results</returns> public static SqlDataReader ExecuteReader(SqlConnection conn, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); // we use a try/catch here because if the method throws an exception we want to // close the connection throw code, because no datareader will exist, hence the // commandBehaviour.CloseConnection will not work try { PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); cmd.Parameters.Clear(); return rdr; } catch { conn.Close(); throw; } } /// <summary> /// Execute a SqlCommand that returns a resultset against the database specified in the connection string /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// SqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param>a valid connection string for a SqlConnection</param> /// <param>the CommandType (stored procedure, text, etc.)</param> /// <param>the stored procedure name or T-SQL command</param> /// <param>an array of SqlParamters used to execute the command</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(ConnectionStringLocalTransaction); // we use a try/catch here because if the method throws an exception we want to // close the connection throw code, because no datareader will exist, hence the // commandBehaviour.CloseConnection will not work try { PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); cmd.Parameters.Clear(); return rdr; } catch { conn.Close(); throw; } } /// <summary> /// add parameter array to the cache /// </summary> /// <param>Key to the parameter cache</param> /// <param>an array of SqlParamters to be cached</param> public static void CacheParameters(string cacheKey, params SqlParameter[] commandParameters) { parmCache[cacheKey] = commandParameters; } /// <summary> /// Retrieve cached parameters /// </summary> /// <param>key used to lookup parameters</param> /// <returns>Cached SqlParamters array</returns> public static SqlParameter[] GetCachedParameters(string cacheKey) { SqlParameter[] cachedParms = (SqlParameter[])parmCache[cacheKey]; if (cachedParms == null) return null; SqlParameter[] clonedParms = new SqlParameter[cachedParms.Length]; for (int i = 0, j = cachedParms.Length; i < j; i++) clonedParms[i] = (SqlParameter)((ICloneable)cachedParms[i]).Clone(); return clonedParms; } /// <summary> /// Prepare a command for execution /// </summary> /// <param>SqlCommand object</param> /// <param>SqlConnection object</param> /// <param>SqlTransaction object</param> /// <param>Cmd type e.g. stored procedure or text</param> /// <param>Command text, e.g. Select * from Products</param> /// <param>SqlParameters to use in the command</param> 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>存储过程名称</param> /// <param>参数类型</param></param> /// <param>参数大小</param> /// <param>参数值</param> /// <returns>新的 parameter 对象</returns> public static SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value) { return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value); } public void MakeInParam(ref string OldString, string ParamName, SqlDbType DbType, int Size, object Value) { OldString = OldString.Replace(ParamName, (string)Value); } /// <summary> /// 传入返回值参数 /// </summary> /// <param>存储过程名称</param> /// <param>参数类型</param> /// <param>参数大小</param> /// <returns>新的 parameter 对象</returns> public static SqlParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size) { return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null); } /// <summary> /// 传入返回值参数 /// </summary> /// <param>存储过程名称</param> /// <param>参数类型</param> /// <param>参数大小</param> /// <returns>新的 parameter 对象</returns> public static SqlParameter MakeReturnParam(string ParamName, SqlDbType DbType, int Size) { return MakeParam(ParamName, DbType, Size, ParameterDirection.ReturnValue, null); } /// <summary> /// 生成存储过程参数 /// </summary> /// <param>存储过程名称</param> /// <param>参数类型</param> /// <param>参数大小</param> /// <param>参数方向</param> /// <param>参数值</param> /// <returns>新的 parameter 对象</returns> public static SqlParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value) { SqlParameter param; if (Size > 0) param = new SqlParameter(ParamName, DbType, Size); else param = new SqlParameter(ParamName, DbType); param.Direction = Direction; if (!(Direction == ParameterDirection.Output && Value == null)) param.Value = Value; return param; } /// <summary> /// 创建 SqlCommand 对象实例(用来返回一个整数值) /// </summary> /// <param>存储过程名</param> /// <param>存储过程参数</param> /// <returns>SqlCommand 对象实例</returns> private static SqlCommand BuildIntCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters) { SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters); command.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null)); return command; } /// <summary> /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值) /// </summary> /// <param>数据库连接</param> /// <param>存储过程名</param> /// <param>存储过程参数</param> /// <returns>SqlCommand</returns> private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters) { SqlCommand command = new SqlCommand(storedProcName, connection); command.CommandType = CommandType.StoredProcedure; foreach (SqlParameter parameter in parameters) { command.Parameters.Add(parameter); } return command; } #region 执行查询语句 返回dataset /// <summary> /// 执行查询语句，返回DataSet /// </summary> /// <param>查询语句</param> /// <returns>DataSet</returns> public static DataSet GetDataSet(string cmdText) { using (SqlConnection conn = new SqlConnection(ConnectionStringLocalTransaction)) { SqlCommand sqlcmd = new SqlCommand(cmdText, conn); sqlcmd.CommandTimeout = 1000; SqlDataAdapter cmd = new SqlDataAdapter(sqlcmd); DataSet ds = new DataSet(); try { conn.Open(); cmd.Fill(ds, "mydt"); return ds; } catch (Exception ex) { conn.Close(); cmd.Dispose(); conn.Dispose(); throw new Exception(ex.Message); } finally { cmd.Dispose(); conn.Close(); conn.Dispose(); } } } #endregion } #endregion
将C:/WINDOWS/Microsoft.NET/Framework/v2.0.50727/InstallUtil.exe 拷贝到执行的目录Bin Debug文件下 然后注册windows服务
复制代码 代码如下:
InstallUtil.exe windowservice.exe
卸载windows服务:
复制代码 代码如下:
InstallUtil.exe -u windowservice.exe