CSharp:Esempio completo Helper
From Aino Wiki
Helpers
Helper Completo di accesso a DB
Aggiungere una reference al progetto per includere la DLL System.Configuration.
using System; using System.Collections.Generic; using System.Configuration; using System.Data; using System.Data.OleDb; using System.Data.SqlClient; using System.Xml; using System.Reflection; namespace Libraries.Data { /// <summary> /// The SqlDataAccess class for common uses of SqlClient. /// </summary> public sealed class SQLDataAccess { #region PROPERTIES public static int CommandTimeOut { get; set; } #endregion #region PRIVATE CONSTRUCTORS //Since this class provides only static methods, make the default constructor private to prevent private SQLDataAccess() { } #endregion #region ADD PARAMETERS public static object CheckForNullString(string text) { if (text == null || text.Trim().Length == 0) { return System.DBNull.Value; } else { return text; } } public static SqlParameter MakeInParam(string ParamName, object Value) { return new SqlParameter(ParamName, Value); } /// <summary> /// Make input param. /// </summary> /// <param name="ParamName">Name of param.</param> /// <param name="DbType">Param type.</param> /// <param name="Size">Param size.</param> /// <param name="Value">Param value.</param> /// <returns>New parameter.</returns> public static SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value) { return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value); } /// <summary> /// Make input param. /// </summary> /// <param name="ParamName">Name of param.</param> /// <param name="DbType">Param type.</param> /// <param name="Size">Param size.</param> /// <returns>New parameter.</returns> public static SqlParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size) { return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null); } /// <summary> /// Make stored procedure param. /// </summary> /// <param name="ParamName">Name of param.</param> /// <param name="DbType">Param type.</param> /// <param name="Size">Param size.</param> /// <param name="Direction">Parm direction.</param> /// <param name="Value">Param value.</param> /// <returns>New 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; } #endregion #region EXECUTE ... #region EXECUTE NON QUERY /// <summary> /// Execute a SqlCommand (that returns no resultset and takes no parameters) against the database specified in /// the connection string. /// </summary> /// <remarks> /// e.g.: /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders"); /// </remarks> /// <param name="connectionString">a valid connection string for a SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <returns>an int representing the number of rows affected by the command</returns> public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText) { //pass through the call providing null for the set of SqlParameters return ExecuteNonQuery(connectionString, commandType, commandText, (SqlParameter[])null); } /// <summary> /// Execute a SqlCommand (that returns no resultset) against the database specified in the connection string /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connectionString">a valid connection string for a SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <param name="commandParameters">an array of SqlParamters used to execute the command</param> /// <returns>an int representing the number of rows affected by the command</returns> public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //create & open a SqlConnection, and dispose of it after we are done. using (SqlConnection cn = new SqlConnection(connectionString)) { cn.Open(); //call the overload that takes a connection in place of the connection string return ExecuteNonQuery(cn, commandType, commandText, commandParameters); } } /// <summary> /// Execute a SqlCommand (that returns no resultset and takes no parameters) against the provided SqlConnection. /// </summary> /// <remarks> /// e.g.: /// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders"); /// </remarks> /// <param name="connection">a valid SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <returns>an int representing the number of rows affected by the command</returns> public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText) { //pass through the call providing null for the set of SqlParameters return ExecuteNonQuery(connection, commandType, commandText, (SqlParameter[])null); } /// <summary> /// Execute a SqlCommand (that returns no resultset) against the specified SqlConnection /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connection">a valid SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <param name="commandParameters">an array of SqlParamters used to execute the command</param> /// <returns>an int representing the number of rows affected by the command</returns> public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //create a command and prepare it for execution SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters); //finally, execute the command. int retval = cmd.ExecuteNonQuery(); // detach the SqlParameters from the command object, so they can be used again. cmd.Parameters.Clear(); return retval; } /// <summary> /// Execute a SqlCommand (that returns no resultset and takes no parameters) against the provided SqlTransaction. /// </summary> /// <remarks> /// e.g.: /// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders"); /// </remarks> /// <param name="transaction">a valid SqlTransaction</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <returns>an int representing the number of rows affected by the command</returns> public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText) { //pass through the call providing null for the set of SqlParameters return ExecuteNonQuery(transaction, commandType, commandText, (SqlParameter[])null); } /// <summary> /// Execute a SqlCommand (that returns no resultset) against the specified SqlTransaction /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="transaction">a valid SqlTransaction</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <param name="commandParameters">an array of SqlParamters used to execute the command</param> /// <returns>an int representing the number of rows affected by the command</returns> public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //create a command and prepare it for execution SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters); //finally, execute the command. int retval = cmd.ExecuteNonQuery(); // detach the SqlParameters from the command object, so they can be used again. cmd.Parameters.Clear(); return retval; } #endregion #region EXECUTE DATASET /// <summary> /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in /// the connection string. /// </summary> /// <remarks> /// e.g.: /// DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders"); /// </remarks> /// <param name="connectionString">a valid connection string for a SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <returns>a dataset containing the resultset generated by the command</returns> public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText) { //pass through the call providing null for the set of SqlParameters return ExecuteDataset(connectionString, commandType, commandText, (SqlParameter[])null); } /// <summary> /// Execute a SqlCommand (that returns a resultset) against the database specified in the connection string /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connectionString">a valid connection string for a SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <param name="commandParameters">an array of SqlParamters used to execute the command</param> /// <returns>a dataset containing the resultset generated by the command</returns> public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //create & open a SqlConnection, and dispose of it after we are done. using (SqlConnection cn = new SqlConnection(connectionString)) { cn.Open(); //call the overload that takes a connection in place of the connection string return ExecuteDataset(cn, commandType, commandText, commandParameters); } } /// <summary> /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection. /// </summary> /// <remarks> /// e.g.: /// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders"); /// </remarks> /// <param name="connection">a valid SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <returns>a dataset containing the resultset generated by the command</returns> public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText) { //pass through the call providing null for the set of SqlParameters return ExecuteDataset(connection, commandType, commandText, (SqlParameter[])null); } /// <summary> /// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connection">a valid SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <param name="commandParameters">an array of SqlParamters used to execute the command</param> /// <returns>a dataset containing the resultset generated by the command</returns> public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //create a command and prepare it for execution SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters); //create the DataAdapter & DataSet SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); //fill the DataSet using default values for DataTable names, etc. da.Fill(ds); // detach the SqlParameters from the command object, so they can be used again. cmd.Parameters.Clear(); //return the dataset return ds; } /// <summary> /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction. /// </summary> /// <remarks> /// e.g.: /// DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders"); /// </remarks> /// <param name="transaction">a valid SqlTransaction</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <returns>a dataset containing the resultset generated by the command</returns> public static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText) { //pass through the call providing null for the set of SqlParameters return ExecuteDataset(transaction, commandType, commandText, (SqlParameter[])null); } /// <summary> /// Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="transaction">a valid SqlTransaction</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <param name="commandParameters">an array of SqlParamters used to execute the command</param> /// <returns>a dataset containing the resultset generated by the command</returns> public static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //create a command and prepare it for execution SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters); //create the DataAdapter & DataSet SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); //fill the DataSet using default values for DataTable names, etc. da.Fill(ds); // detach the SqlParameters from the command object, so they can be used again. cmd.Parameters.Clear(); //return the dataset return ds; } #endregion #region EXECUTE DATATABLE /// <summary> /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in /// the connection string. /// </summary> /// <remarks> /// e.g.: /// DataTable dt = ExecuteDataTable(connString, CommandType.StoredProcedure, "GetOrders"); /// </remarks> /// <param name="connectionString">a valid connection string for a SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <returns>a DataTable containing the resultset generated by the command</returns> public static DataTable ExecuteDataTable(string connectionString, CommandType commandType, string commandText) { //pass through the call providing null for the set of SqlParameters return ExecuteDataTable(connectionString, commandType, commandText, (SqlParameter[])null); } /// <summary> /// Execute a SqlCommand (that returns a resultset) against the database specified in the connection string /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// DataTable dt = ExecuteDataTable(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connectionString">a valid connection string for a SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <param name="commandParameters">an array of SqlParamters used to execute the command</param> /// <returns>a DataTable containing the resultset generated by the command</returns> public static DataTable ExecuteDataTable(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //create & open a SqlConnection, and dispose of it after we are done. using (SqlConnection cn = new SqlConnection(connectionString)) { cn.Open(); //call the overload that takes a connection in place of the connection string return ExecuteDataTable(cn, commandType, commandText, commandParameters); } } /// <summary> /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection. /// </summary> /// <remarks> /// e.g.: /// DataTable dt = ExecuteDataTable(conn, CommandType.StoredProcedure, "GetOrders"); /// </remarks> /// <param name="connection">a valid SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <returns>a DataTable containing the resultset generated by the command</returns> public static DataTable ExecuteDataTable(SqlConnection connection, CommandType commandType, string commandText) { //pass through the call providing null for the set of SqlParameters return ExecuteDataTable(connection, commandType, commandText, (SqlParameter[])null); } /// <summary> /// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// DataTable dt = ExecuteDataTable(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connection">a valid SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <param name="commandParameters">an array of SqlParamters used to execute the command</param> /// <returns>a DataTable containing the resultset generated by the command</returns> public static DataTable ExecuteDataTable(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //create a command and prepare it for execution SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters); //create the DataAdapter & DataTable SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); //fill the DataTable using default values for DataTable names, etc. da.Fill(dt); // detach the SqlParameters from the command object, so they can be used again. cmd.Parameters.Clear(); //return the DataTable return dt; } /// <summary> /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction. /// </summary> /// <remarks> /// e.g.: /// DataTable dt = ExecuteDataTable(trans, CommandType.StoredProcedure, "GetOrders"); /// </remarks> /// <param name="transaction">a valid SqlTransaction</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <returns>a DataTable containing the resultset generated by the command</returns> public static DataTable ExecuteDataTable(SqlTransaction transaction, CommandType commandType, string commandText) { //pass through the call providing null for the set of SqlParameters return ExecuteDataTable(transaction, commandType, commandText, (SqlParameter[])null); } /// <summary> /// Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// DataTable dt = ExecuteDataTable(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="transaction">a valid SqlTransaction</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <param name="commandParameters">an array of SqlParamters used to execute the command</param> /// <returns>a DataTable containing the resultset generated by the command</returns> public static DataTable ExecuteDataTable(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //create a command and prepare it for execution SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters); //create the DataAdapter & DataTable SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); //fill the DataTable using default values for DataTable names, etc. da.Fill(dt); // detach the SqlParameters from the command object, so they can be used again. cmd.Parameters.Clear(); //return the DataTable return dt; } #endregion #region EXECUTE READER /// <summary> /// this enum is used to indicate whether the connection was provided by the caller, or created by SqlDataAccess, so that /// we can set the appropriate CommandBehavior when calling ExecuteReader() /// </summary> private enum SqlConnectionOwnership { /// <summary>Connection is owned and managed by SqlDataAccess</summary> Internal, /// <summary>Connection is owned and managed by the caller</summary> External } /// <summary> /// Create and prepare a SqlCommand, and call ExecuteReader with the appropriate CommandBehavior. /// </summary> /// <remarks> /// If we created and opened the connection, we want the connection to be closed when the DataReader is closed. /// /// If the caller provided the connection, we want to leave it to them to manage. /// </remarks> /// <param name="connection">a valid SqlConnection, on which to execute this command</param> /// <param name="transaction">a valid SqlTransaction, or 'null'</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <param name="commandParameters">an array of SqlParameters to be associated with the command or 'null' if no parameters are required</param> /// <param name="connectionOwnership">indicates whether the connection parameter was provided by the caller, or created by SqlDataAccess</param> /// <returns>SqlDataReader containing the results of the command</returns> private static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership) { //create a command and prepare it for execution SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters); //create a reader SqlDataReader dr; // call ExecuteReader with the appropriate CommandBehavior if (connectionOwnership == SqlConnectionOwnership.External) { dr = cmd.ExecuteReader(); } else { dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); } // detach the SqlParameters from the command object, so they can be used again. cmd.Parameters.Clear(); return dr; } /// <summary> /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in /// the connection string. /// </summary> /// <remarks> /// e.g.: /// SqlDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders"); /// </remarks> /// <param name="connectionString">a valid connection string for a SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <returns>a SqlDataReader containing the resultset generated by the command</returns> public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText) { //pass through the call providing null for the set of SqlParameters return ExecuteReader(connectionString, commandType, commandText, (SqlParameter[])null); } /// <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 dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connectionString">a valid connection string for a SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <param name="commandParameters">an array of SqlParamters used to execute the command</param> /// <returns>a SqlDataReader containing the resultset generated by the command</returns> public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //create & open a SqlConnection SqlConnection cn = new SqlConnection(connectionString); cn.Open(); try { //call the private overload that takes an internally owned connection in place of the connection string return ExecuteReader(cn, null, commandType, commandText, commandParameters, SqlConnectionOwnership.Internal); } catch { //if we fail to return the SqlDatReader, we need to close the connection ourselves cn.Close(); throw; } } /// <summary> /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection. /// </summary> /// <remarks> /// e.g.: /// SqlDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders"); /// </remarks> /// <param name="connection">a valid SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <returns>a SqlDataReader containing the resultset generated by the command</returns> public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText) { //pass through the call providing null for the set of SqlParameters return ExecuteReader(connection, commandType, commandText, (SqlParameter[])null); } /// <summary> /// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// SqlDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connection">a valid SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <param name="commandParameters">an array of SqlParamters used to execute the command</param> /// <returns>a SqlDataReader containing the resultset generated by the command</returns> public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //pass through the call to the private overload using a null transaction value and an externally owned connection return ExecuteReader(connection, (SqlTransaction)null, commandType, commandText, commandParameters, SqlConnectionOwnership.External); } /// <summary> /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction. /// </summary> /// <remarks> /// e.g.: /// SqlDataReader dr = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders"); /// </remarks> /// <param name="transaction">a valid SqlTransaction</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <returns>a SqlDataReader containing the resultset generated by the command</returns> public static SqlDataReader ExecuteReader(SqlTransaction transaction, CommandType commandType, string commandText) { //pass through the call providing null for the set of SqlParameters return ExecuteReader(transaction, commandType, commandText, (SqlParameter[])null); } /// <summary> /// Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// SqlDataReader dr = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="transaction">a valid SqlTransaction</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <param name="commandParameters">an array of SqlParamters used to execute the command</param> /// <returns>a SqlDataReader containing the resultset generated by the command</returns> public static SqlDataReader ExecuteReader(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //pass through to private overload, indicating that the connection is owned by the caller return ExecuteReader(transaction.Connection, transaction, commandType, commandText, commandParameters, SqlConnectionOwnership.External); } #endregion #region EXECUTE SCALAR /// <summary> /// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the database specified in /// the connection string. /// </summary> /// <remarks> /// e.g.: /// int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount"); /// </remarks> /// <param name="connectionString">a valid connection string for a SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns> public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText) { //pass through the call providing null for the set of SqlParameters return ExecuteScalar(connectionString, commandType, commandText, (SqlParameter[])null); } /// <summary> /// Execute a SqlCommand (that returns a 1x1 resultset) against the database specified in the connection string /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connectionString">a valid connection string for a SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <param name="commandParameters">an array of SqlParamters used to execute the command</param> /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns> public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //create & open a SqlConnection, and dispose of it after we are done. using (SqlConnection cn = new SqlConnection(connectionString)) { cn.Open(); //call the overload that takes a connection in place of the connection string return ExecuteScalar(cn, commandType, commandText, commandParameters); } } /// <summary> /// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the provided SqlConnection. /// </summary> /// <remarks> /// e.g.: /// int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount"); /// </remarks> /// <param name="connection">a valid SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns> public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText) { //pass through the call providing null for the set of SqlParameters return ExecuteScalar(connection, commandType, commandText, (SqlParameter[])null); } /// <summary> /// Execute a SqlCommand (that returns a 1x1 resultset) against the specified SqlConnection /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connection">a valid SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <param name="commandParameters">an array of SqlParamters used to execute the command</param> /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns> public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //create a command and prepare it for execution SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters); //execute the command & return the results object retval = cmd.ExecuteScalar(); // detach the SqlParameters from the command object, so they can be used again. cmd.Parameters.Clear(); return retval; } /// <summary> /// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the provided SqlTransaction. /// </summary> /// <remarks> /// e.g.: /// int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount"); /// </remarks> /// <param name="transaction">a valid SqlTransaction</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns> public static object ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText) { //pass through the call providing null for the set of SqlParameters return ExecuteScalar(transaction, commandType, commandText, (SqlParameter[])null); } /// <summary> /// Execute a SqlCommand (that returns a 1x1 resultset) against the specified SqlTransaction /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="transaction">a valid SqlTransaction</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <param name="commandParameters">an array of SqlParamters used to execute the command</param> /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns> public static object ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //create a command and prepare it for execution SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters); //execute the command & return the results object retval = cmd.ExecuteScalar(); // detach the SqlParameters from the command object, so they can be used again. cmd.Parameters.Clear(); return retval; } #endregion #region EXECUTE XML READER /// <summary> /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection. /// </summary> /// <remarks> /// e.g.: /// XmlReader r = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders"); /// </remarks> /// <param name="connection">a valid SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command using "FOR XML AUTO"</param> /// <returns>an XmlReader containing the resultset generated by the command</returns> public static XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType, string commandText) { //pass through the call providing null for the set of SqlParameters return ExecuteXmlReader(connection, commandType, commandText, (SqlParameter[])null); } /// <summary> /// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// XmlReader r = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connection">a valid SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command using "FOR XML AUTO"</param> /// <param name="commandParameters">an array of SqlParamters used to execute the command</param> /// <returns>an XmlReader containing the resultset generated by the command</returns> public static XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //create a command and prepare it for execution SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters); //create the DataAdapter & DataSet XmlReader retval = cmd.ExecuteXmlReader(); // detach the SqlParameters from the command object, so they can be used again. cmd.Parameters.Clear(); return retval; } /// <summary> /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction. /// </summary> /// <remarks> /// e.g.: /// XmlReader r = ExecuteXmlReader(trans, CommandType.StoredProcedure, "GetOrders"); /// </remarks> /// <param name="transaction">a valid SqlTransaction</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command using "FOR XML AUTO"</param> /// <returns>an XmlReader containing the resultset generated by the command</returns> public static XmlReader ExecuteXmlReader(SqlTransaction transaction, CommandType commandType, string commandText) { //pass through the call providing null for the set of SqlParameters return ExecuteXmlReader(transaction, commandType, commandText, (SqlParameter[])null); } /// <summary> /// Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// XmlReader r = ExecuteXmlReader(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="transaction">a valid SqlTransaction</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command using "FOR XML AUTO"</param> /// <param name="commandParameters">an array of SqlParamters used to execute the command</param> /// <returns>an XmlReader containing the resultset generated by the command</returns> public static XmlReader ExecuteXmlReader(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //create a command and prepare it for execution SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters); //create the DataAdapter & DataSet XmlReader retval = cmd.ExecuteXmlReader(); // detach the SqlParameters from the command object, so they can be used again. cmd.Parameters.Clear(); return retval; } #endregion #endregion public static T GetEntity<T>(DataRow dr) { bool columnCanNotCorrespond = true; return GetEntity<T>(dr, columnCanNotCorrespond); } /// <summary> /// Riempie una Entità con i valori presenti in un DataRow automaticamente. Se un valore è NULLO inserirà il valore di default. /// L'automatismo funzione solo se i nomi delle colonne (campi del DataBase) corrispondono ai nomi /// delle properties se una Property non ha colonna del DataRow semplicemente è valorizzata al Default /// (come dopo una istanzizione dell'oggetto mediante "new"). Se una colonna esiste ma non c'è la /// corrispondente proprietà la segnalazione dell'eccezione dipenderà dal flag: columnCanNotCorrespond /// </summary> /// <typeparam name="T"></typeparam> /// <param name="dr"></param> /// <param name="columnCanNotCorrespond">Se true, non scatta eccezione se c'è una colonna che non è corrispondente ad una Property</param> /// <returns></returns> public static T GetEntity<T>(DataRow dr, bool columnCanNotCorrespond) { Type entityType = typeof(T); T entity = (T)entityType.Assembly.CreateInstance(entityType.FullName); if (columnCanNotCorrespond) { foreach (DataColumn dc in dr.Table.Columns) { object columnValue = dr[dc.ColumnName]; //La Property Esiste? if (entity.GetType().GetProperty(dc.ColumnName, BindingFlags.Public | BindingFlags.Instance | BindingFlags.IgnoreCase) != null) { //Assegnazione condizionata affinchè rimanga il valore di Default nel caso il valore da inserire sia nullo if (columnValue != DBNull.Value) { entity.GetType().GetProperty(dc.ColumnName, BindingFlags.Public | BindingFlags.Instance | BindingFlags.IgnoreCase).SetValue(entity, columnValue, null); } } } } else //Scatterà eccezione se la Property non corrisponde alla colonna! { foreach (DataColumn dc in dr.Table.Columns) { object columnValue = dr[dc.ColumnName]; //Assegnazione condizionata affinchè rimanga il valore di Default nel caso il valore da inserire sia nullo if (columnValue != DBNull.Value) { entity.GetType().GetProperty(dc.ColumnName, BindingFlags.Public | BindingFlags.Instance | BindingFlags.IgnoreCase).SetValue(entity, columnValue, null); } } } return (T)entity; } /* public static T GetEntity<T>(DataRow dr, bool columnCanNotCorrespond) { Type entityType = typeof(T); T entity = (T)entityType.Assembly.CreateInstance(entityType.FullName); if (columnCanNotCorrespond) { foreach (DataColumn dc in dr.Table.Columns) { object columnValue = dr[dc.ColumnName]; if (entity.GetType().GetProperty(dc.ColumnName) != null) //La Property Esiste? { //Assegnazione condizionata affinchè rimanga il valore di Default nel caso il valore da inserire sia nullo if (columnValue != DBNull.Value) { entity.GetType().GetProperty(dc.ColumnName).SetValue(entity, columnValue, null); } } } } else //Scatterà eccezione se la Property non corrisponde alla colonna! { foreach (DataColumn dc in dr.Table.Columns) { object columnValue = dr[dc.ColumnName]; //Assegnazione condizionata affinchè rimanga il valore di Default nel caso il valore da inserire sia nullo if (columnValue != DBNull.Value) { entity.GetType().GetProperty(dc.ColumnName).SetValue(entity, columnValue, null); } } } return (T)entity; } */ public static Dictionary<string, string> GetConnectionStringInfo(string connStringName) { Dictionary<string, string> dicConnInfo = null; try { ConnectionStringSettingsCollection connectionsList = ConfigurationManager.ConnectionStrings; if (connectionsList.Count != 0) { // Get the collection elements. foreach (ConnectionStringSettings connectionParam in connectionsList) { string name = connectionParam.Name; string provider = connectionParam.ProviderName; string connectionString = connectionParam.ConnectionString; if (name == connStringName) { string[] arrConnStrParts = connectionString.Split(';'); if (arrConnStrParts.Length > 0) { dicConnInfo = new Dictionary<string, string>(); } foreach (string param in arrConnStrParts) { string[] arrParams = param.Split('='); dicConnInfo.Add(arrParams[0], arrParams[1]); // es: dicConnInfo.Add("Data Source", "172.19.27.16") } } } } } catch { } return dicConnInfo; } #region DATA UTILITIES /// <summary> /// Data la query SQL sostituisce il testo dei parametri simbolici con il valore degli stessi considerando tutti come valore di tipo testo. /// Funziona ad es su query su DB MS Accesss /// </summary> /// <param name="qrySQL"></param> /// <param name="parColl"></param> /// <returns></returns> public static string FillValueInSQLQry(string qrySQL, OleDbParameterCollection parColl) { string value = string.Empty; foreach (OleDbParameter p in parColl) { value = p.Value == null ? string.Empty : p.Value.ToString(); qrySQL = qrySQL.Replace(p.ParameterName, string.Format("'{0}'", value)); } return qrySQL; } /// <summary> /// Data la query SQL sostituisce il testo dei parametri simbolici con il valore degli stessi considerando tutti come valore di tipo testo. /// Funziona ad es su query su DB MS SQL Server /// </summary> /// <param name="qrySQL"></param> /// <param name="parColl"></param> /// <returns></returns> public static string FillValueInSQLQry(string qrySQL, SqlParameterCollection parColl) { string value = string.Empty; foreach (SqlParameter p in parColl) { value = p.Value == null ? string.Empty : p.Value.ToString(); qrySQL = qrySQL.Replace(p.ParameterName, string.Format("'{0}'", value)); } return qrySQL; } #region Metodi per le TABELLE del DB public static List<TableInfo> GetTableInfoEntity(string connectionString) { string dbName = string.Empty; string objectType = string.Empty; string patternSx = string.Empty; string patternDx = string.Empty; string patternMiddle = string.Empty; return GetTableInfoEntity(connectionString, dbName, objectType, patternSx, patternDx, patternMiddle); } public static List<TableInfo> GetTableInfoEntity(string connectionString, string dbName) { string objectType = string.Empty; string patternSx = string.Empty; string patternDx = string.Empty; string patternMiddle = string.Empty; return GetTableInfoEntity(connectionString, dbName, objectType, patternSx, patternDx, patternMiddle); } public static List<TableInfo> GetTableInfoEntity(string connectionString, string dbName, string objectType, string patternSx, string patternDx, string patternMiddle) { DataTable dt = GetTableInfo(connectionString, dbName, objectType, patternSx, patternDx, patternMiddle); List<TableInfo> lti = new List<TableInfo>(); foreach (DataRow row in dt.Rows) { lti.Add(TableInfo.GetEntityByDataRow(row)); } return lti; } /// <summary> /// Restituisce l'elenco delle tabelle /// </summary> /// <param name="connectionString">Stringa di connessione al DB, OBBLIGATORIA</param> /// <param name="dbName">Nome del Database, se non indicato si intenderà interogare il "corrente"</param> /// /// <param name="objectType">Tipo di oggetto: Tabella, Vista. Se non indicato si intende ottenere l'elenco delle tabelle</param> /// <param name="patternSx">Filtra restituiendo tutti i nomi tabella che iniziano per 'patternSx'</param> /// <param name="patternDx">Filtra restituiendo tutti i nomi tabella che finiscono per 'patternDx'</param> /// /// <param name="patternMiddle">Filtra restituiendo tutti i nomi tabella che contengono la stringa 'patternMiddle'</param> /// <returns></returns> public static DataTable GetTableInfo(string connectionString, string dbName, string objectType, string patternSx, string patternDx, string patternMiddle) { DataTable dt = new DataTable(); string strQrySQL = string.Empty; string strUseDBName = string.IsNullOrEmpty(dbName) ? string.Empty : "USE " + dbName + ";"; string strObjectType = string.IsNullOrEmpty(objectType) ? "BASE TABLE" : objectType; string strWherePattern = string.Empty; if (string.IsNullOrEmpty(connectionString)) { throw new Exception("Non fornita la stringa di connessione per accedere al DB di cui si vuole l'elenco nomi tabella."); } //Costruzione del filtro sul nomeTabella if (!string.IsNullOrEmpty(patternSx) || !string.IsNullOrEmpty(patternDx) || !string.IsNullOrEmpty(patternMiddle)) { strWherePattern = !string.IsNullOrEmpty(patternSx) ? string.Format(" AND T.TABLE_NAME LIKE '{0}%'", patternSx) : !string.IsNullOrEmpty(patternSx) ? string.Format(" AND T.TABLE_NAME LIKE '%{0}'", patternDx) : !string.IsNullOrEmpty(patternMiddle) ? string.Format(" AND T.TABLE_NAME LIKE '%{0}%'", patternMiddle) : string.Empty; } using (SqlConnection cnn = new SqlConnection(connectionString)) { strQrySQL = String.Format( @"{0} SELECT A.NameWithSchema, A.[RowCount], A.MegaBytes FROM (SELECT NameWithSchema = T.TABLE_SCHEMA + '.' + T.TABLE_NAME ,[RowCount] = SUM(SP.[ROWS]) ,MegaBytes = (8 * SUM(CASE WHEN SAU.TYPE != 1 THEN SAU.used_pages WHEN SP.index_id < 2 THEN SAU.data_pages ELSE 0 END)) / 1024 FROM INFORMATION_SCHEMA.TABLES T JOIN sys.partitions SP ON sp.object_id = OBJECT_ID(T.TABLE_SCHEMA + '.' + T.TABLE_NAME) JOIN sys.allocation_units SAU ON SAU.container_id = SP.partition_id WHERE TABLE_TYPE = '{1}' {2} GROUP BY T.TABLE_SCHEMA + '.' + T.TABLE_NAME ) A ORDER BY NameWithSchema;", strUseDBName, strObjectType, strWherePattern); cnn.Open(); using (SqlCommand cmd = new SqlCommand()) { cmd.Connection = cnn; cmd.CommandType = CommandType.Text; cmd.CommandText = strQrySQL; using (SqlDataAdapter da = new SqlDataAdapter(cmd)) { da.Fill(dt); } } } return dt; } #endregion #region Metodi per CAMPI del DB public static string GetFieldNameFirstPrimaryKey(string tableName, string connectionString) { string dbName = string.Empty; return GetFieldNameFirstPrimaryKey(tableName, dbName, connectionString); } public static string GetFieldNameFirstPrimaryKey(string tableName, string dbName, string connectionString) { string primaryKeyName = string.Empty; string strUseDBName = string.IsNullOrEmpty(dbName) ? string.Empty : "USE " + dbName + "; "; string selectTSqlCmd = strUseDBName + "sp_pkeys '" + tableName + "'"; DataTable dt = new DataTable(); using (SqlConnection cnn = new SqlConnection(connectionString)) { cnn.Open(); using (SqlCommand cmd = new SqlCommand()) { cmd.Connection = cnn; cmd.CommandType = CommandType.Text; cmd.CommandText = selectTSqlCmd; using (SqlDataAdapter da = new SqlDataAdapter(cmd)) { da.Fill(dt); } if (dt != null && dt.Rows.Count > 0) { primaryKeyName = dt.Rows[0]["COLUMN_NAME"].ToString(); } } } return primaryKeyName; } public static List<TableStruct> GetTableStructEntity(string tableName, string connectionString) { DataTable dt = GetTableStruct(tableName, connectionString); List<TableStruct> lts = new List<TableStruct>(); foreach (DataRow row in dt.Rows) { lts.Add(TableStruct.GetEntityByDataRow(row)); } return lts; } public static List<TableStruct> GetTableStructEntity(string tableName) { DataTable dt = GetTableStruct(tableName); List<TableStruct> lts = new List<TableStruct>(); foreach (DataRow row in dt.Rows) { lts.Add(TableStruct.GetEntityByDataRow(row)); } return lts; } /// <summary> /// Lavora come default sul DB di Importazione /// </summary> /// <param name="tableName"></param> /// <returns></returns> public static DataTable GetTableStruct(string tableName) { string connectionString = string.Empty; return GetTableStruct(tableName, connectionString); } /// <summary> /// Restituisce un minimo di informazioni sulla struttura di una Tabella tra cui il nome dei campi /// </summary> /// <returns></returns> public static DataTable GetTableStruct(string tableName, string connectionString) //, bool addTopBlackItem { DataTable dt = new DataTable(); string selectTSqlCmd = string.Empty; connectionString = string.IsNullOrEmpty(connectionString) ? ConfigurationManager.ConnectionStrings["MWIMPORT"].ToString() : connectionString; using (SqlConnection cnn = new SqlConnection(connectionString)) { //ATTENZIONE, NON è ATTENDIBILE --> (SELECT TOP 1 ISC.data_type FROM information_schema.columns AS ISC WHERE sc.name = ISC.COLUMN_NAME) AS typeName, selectTSqlCmd = String.Format( @"SELECT SC.[colid], SC.[name], SC.[colorder], SC.[xtype], (CASE WHEN SC.[xtype] = 175 THEN 'CHAR' WHEN SC.[xtype] = 239 THEN 'NCHAR' WHEN SC.[xtype] = 167 THEN 'VARCHAR' WHEN SC.[xtype] = 231 THEN 'NVARCHAR' WHEN SC.[xtype] = 35 THEN 'TEXT' WHEN SC.[xtype] = 99 THEN 'NTEXT' WHEN SC.[xtype] = 241 THEN 'XML' WHEN SC.[xtype] = 104 THEN 'BIT' WHEN SC.[xtype] = 48 THEN 'TINYINT' WHEN SC.[xtype] = 52 THEN 'SMALLINT' WHEN SC.[xtype] = 56 THEN 'INT' WHEN SC.[xtype] = 127 THEN 'BIGINT' WHEN SC.[xtype] = 106 THEN 'DECIMAL' WHEN SC.[xtype] = 122 THEN 'SMALLMONEY' WHEN SC.[xtype] = 60 THEN 'MONEY' WHEN SC.[xtype] = 62 THEN 'FLOAT' WHEN SC.[xtype] = 59 THEN 'REAL' WHEN SC.[xtype] = 173 THEN 'BINARY' WHEN SC.[xtype] = 165 THEN 'VARBINARY' WHEN SC.[xtype] = 108 THEN 'NUMERIC' WHEN SC.[xtype] = 34 THEN 'IMAGE' WHEN SC.[xtype] = 36 THEN 'UNIQUEIDENTIFIER' WHEN SC.[xtype] = 40 THEN 'DATE' WHEN SC.[xtype] = 41 THEN 'TIME' WHEN SC.[xtype] = 61 THEN 'DATETIME' WHEN SC.[xtype] = 42 THEN 'DATETIME2' WHEN SC.[xtype] = 43 THEN 'DATETIMEOFFSET' WHEN SC.[xtype] = 58 THEN 'SMALLDATETIME' WHEN SC.[xtype] = 189 THEN 'TIMESTAMP' WHEN SC.[xtype] = 98 THEN 'SQL_VARIANT' ELSE '?' END) AS typeName, SC.[length], SC.[isnullable], (CASE WHEN EXISTS(SELECT col.name FROM sys.tables TA INNER JOIN sys.indexes IND ON IND.object_id = TA.object_id INNER JOIN sys.index_columns INDCOL ON INDCOL.object_id = TA.object_id AND INDCOL.index_id = IND.index_id INNER JOIN sys.columns COL ON COL.object_id = TA.object_id AND COL.column_id = INDCOL.column_id WHERE IND.is_primary_key = 1 AND TA.name = '{0}' AND COL.name = SC.[name]) THEN 1 ELSE 0 END) AS IsPrimaryKey FROM syscolumns AS SC (NOLOCK) WHERE id = (SELECT id FROM sysobjects (NOLOCK) WHERE type = 'U' AND [NAME] = '{0}')", tableName); cnn.Open(); using (SqlCommand cmd = new SqlCommand()) { cmd.Connection = cnn; cmd.CommandType = CommandType.Text; cmd.CommandText = selectTSqlCmd; using (SqlDataAdapter da = new SqlDataAdapter(cmd)) { da.Fill(dt); } } } //Si inserisce comunque un item VUOTO (utile nelle DDL) if (dt != null) { DataRow dr = dt.NewRow(); dt.Rows.InsertAt(dr, 0); } return dt; } /// <summary> /// Restituisce le info sul tipo di un campo di una tabella: typo e lunghezza. Legato alla GetTableStruct() !!! /// </summary> /// <param name="dtTableStruct">Struttura ottenuta mediante la GetTableStruct(tableName). Un rigo DataRow è un campo</param> /// <param name="fieldName"></param> /// <returns></returns> public static string GetFieldInfoFromTableStruct(DataTable dtTableStruct, string fieldName) { string descriptionTypeInfo = string.Empty; string fieldInfoName = string.Empty; try { if (!string.IsNullOrEmpty(fieldName) && dtTableStruct != null && dtTableStruct.Rows.Count > 0) { foreach (DataRow dr in dtTableStruct.Rows) { fieldInfoName = dr["name"].ToString(); if (fieldInfoName.ToUpper() == fieldName.ToUpper()) //Trovato? { string strNullable = dr["isnullable"].ToString() == "1" ? "NULL" : "NOT NULL"; descriptionTypeInfo = dr["typeName"].ToString() + " (" + dr["length"].ToString() + ") " + strNullable; break; } } } } catch (Exception ex) { Console.WriteLine(ex.Message); } return descriptionTypeInfo; } public static string CheckNull(object obj) { return (string)obj; } //????? public static string CheckNull(DBNull obj) { return null; } #endregion #endregion #region PRIVATE UTILITIES /// <summary> /// This method is used to attach array of SqlParameters to a SqlCommand. /// /// This method will assign a value of DbNull to any parameter with a direction of /// InputOutput and a value of null. /// /// This behavior will prevent default values from being used, but /// this will be the less common case than an intended pure output parameter (derived as InputOutput) /// where the user provided no input value. /// </summary> /// <param name="command">The command to which the parameters will be added</param> /// <param name="commandParameters">an array of SqlParameters tho be added to command</param> private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters) { foreach (SqlParameter p in commandParameters) { //check for derived output value with no value assigned if ((p.Direction == ParameterDirection.InputOutput) && (p.Value == null)) { p.Value = DBNull.Value; } command.Parameters.Add(p); } } /// <summary> /// This method assigns an array of values to an array of SqlParameters. /// </summary> /// <param name="commandParameters">array of SqlParameters to be assigned values</param> /// <param name="parameterValues">array of Components holding the values to be assigned</param> private static void AssignParameterValues(SqlParameter[] commandParameters, object[] parameterValues) { if ((commandParameters == null) || (parameterValues == null)) { //do nothing if we get no data return; } // we must have the same number of values as we pave parameters to put them in if (commandParameters.Length != parameterValues.Length) { throw new ArgumentException("Parameter count does not match Parameter Value count."); } //iterate through the SqlParameters, assigning the values from the corresponding position in the //value array for (int i = 0, j = commandParameters.Length; i < j; i++) { commandParameters[i].Value = parameterValues[i]; } } /// <summary> /// This method opens (if necessary) and assigns a connection, transaction, command type and parameters /// to the provided command. /// </summary> /// <param name="command">the SqlCommand to be prepared</param> /// <param name="connection">a valid SqlConnection, on which to execute this command</param> /// <param name="transaction">a valid SqlTransaction, or 'null'</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <param name="commandParameters">an array of SqlParameters to be associated with the command or 'null' if no parameters are required</param> private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters) { //if the provided connection is not open, we will open it if (connection.State != ConnectionState.Open) { connection.Open(); } //associate the connection with the command command.Connection = connection; //set the command text (stored procedure name or SQL statement) command.CommandText = commandText; //if we were provided a transaction, assign it. if (transaction != null) { command.Transaction = transaction; } //if we were provided a command timeout, assign it. if (CommandTimeOut > 0) { command.CommandTimeout = CommandTimeOut; } //set the command type command.CommandType = commandType; //attach the command parameters if they are provided if (commandParameters != null) { AttachParameters(command, commandParameters); } return; } #endregion } public class TableInfo { #region PROPERTIES public string Name { get; set; } public string NameWithSchema { get; set; } public double RowCount { get; set; } public double MegaBytes { get; set; } #endregion public static TableInfo GetEntityByDataRow(DataRow row) { TableInfo ti = new TableInfo(); ti.NameWithSchema = row["NameWithSchema"] != DBNull.Value ? row["NameWithSchema"].ToString() : string.Empty; int to = ti.NameWithSchema.IndexOf('.'); if (to >= 0) { ti.Name = ti.NameWithSchema.Substring(to + 1); } else { ti.Name = ti.NameWithSchema; } ti.RowCount = row["RowCount"] != DBNull.Value ? double.Parse(row["RowCount"].ToString()) : 0; ti.MegaBytes = row["MegaBytes"] != DBNull.Value ? double.Parse(row["MegaBytes"].ToString()) : 0; return ti; } } public class TableStruct { #region PROPERTIES public int ColId { get; set; } public string Name { get; set; } public int ColOrder { get; set; } public string XType { get; set; } public string TypeName { get; set; } public int Length { get; set; } public bool IsNullable { get; set; } public bool IsPrimaryKey { get; set; } #endregion public static TableStruct GetEntityByDataRow(DataRow row) { TableStruct ts = new TableStruct(); ts.ColId = row["colId"] != DBNull.Value ? int.Parse(row["colId"].ToString()) : 0; ts.Name = row["name"] != DBNull.Value ? row["name"].ToString() : string.Empty; ts.ColOrder = row["colorder"] != DBNull.Value ? int.Parse(row["colorder"].ToString()) : 0; ts.XType = row["xtype"] != DBNull.Value ? row["xtype"].ToString() : string.Empty; ts.TypeName = row["typeName"] != DBNull.Value ? row["typeName"].ToString() : string.Empty; ts.Length = row["length"] != DBNull.Value ? int.Parse(row["length"].ToString()) : 0; ts.IsNullable = row["isnullable"] != DBNull.Value ? row["isnullable"].ToString() == "1" : false; ts.IsPrimaryKey = row["IsPrimaryKey"] != DBNull.Value ? row["IsPrimaryKey"].ToString() == "1" : false; return ts; } } }
