C# VistaDBCommand类代码示例

        /// <summary>
        /// Call the export schema and data sql function to write out the xml file
        /// </summary>
        /// <param name="outputFilename">Name of the file to write to disk</param>
        public static void CallExportSchemaAndDataSQL(string outputFilename)
        {
            Console.WriteLine("Attempting to execute CLR Proc ExportSchemaAndData");
            using (VistaDBConnection connection = new VistaDBConnection())
            {
                connection.ConnectionString = SampleRunner.ConnectionString;
                connection.Open();

                try
                {
                    using (VistaDBCommand command = new VistaDBCommand())
                    {
                        // Straight forward way to call a function is just using SELECT
                        // You cannot EXEC a SqlFunction, and you cannot set the command here to be a stored proc
                        // Setting this command to a stored proc is a common error, the two are not the same
                        // SqlFunction = SELECT to call
                        // SqlProcdure = EXEC or direct call using StoredProcedure command type
                        command.Connection = connection;
                        command.CommandText = string.Format("SELECT ExportSchemaAndData('{0}');", outputFilename);
                        // This command does not return anything in the rowset, so just execute non query
                        command.ExecuteNonQuery();
                    }
                    Console.WriteLine(string.Format("Schema and Data export to {0}\\{1}.xml", Directory.GetCurrentDirectory(), outputFilename));
                }
                catch (Exception e)
                {
                    Console.WriteLine("Failed to execute CLR-Proc ExportSchemaAndData, Reason: " + e.Message);
                }
            }
        }

        protected static string GetFromStatement(esDataRequest request, esDynamicQuerySerializable query, VistaDBCommand cmd, ref int pindex)
        {
            IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;

            string sql = String.Empty;

            if (iQuery.InternalFromQuery == null)
            {
                sql = Shared.CreateFullName(query);

                if (iQuery.JoinAlias != " ")
                {
                    sql += " " + iQuery.JoinAlias;
                }
            }
            else
            {
                IDynamicQuerySerializableInternal iSubQuery = iQuery.InternalFromQuery as IDynamicQuerySerializableInternal;

                iSubQuery.IsInSubQuery = true;

                sql += "(";
                sql += BuildQuery(request, iQuery.InternalFromQuery, cmd, ref pindex);
                sql += ")";

                if (iSubQuery.SubQueryAlias != " ")
                {
                    sql += " AS " + iSubQuery.SubQueryAlias;
                }

                iSubQuery.IsInSubQuery = false;
            }

            return sql;
        }

        public static VistaDBCommand PrepareCommand(esDataRequest request)
        {
            esDynamicQuerySerializable query = request.DynamicQuery;
            VistaDBCommand cmd = new VistaDBCommand();

            int pindex = NextParamIndex(cmd);

            string sql = BuildQuery(request, query, cmd, ref pindex);

            cmd.CommandText = sql;
            return cmd;
        }

        public static void AddParameters(VistaDBCommand cmd, tgDataRequest request)
        {
            if (request.QueryType == tgQueryType.Text && request.QueryText != null && request.QueryText.Contains("{0}"))
            {
                int i = 0;
                string token = String.Empty;
                string sIndex = String.Empty;
                string param = String.Empty;

                foreach (tgParameter esParam in request.Parameters)
                {
                    sIndex = i.ToString();
                    token = '{' + sIndex + '}';
                    param = Delimiters.Param + "p" + sIndex;
                    request.QueryText = request.QueryText.Replace(token, param);
                    i++;

                    cmd.Parameters.Add(Delimiters.Param + esParam.Name, esParam.Value);
                }
            }
            else
            {
                VistaDBParameter param;

                foreach (tgParameter esParam in request.Parameters)
                {
                    param = cmd.Parameters.Add(Delimiters.Param + esParam.Name, esParam.Value);

                    switch (esParam.Direction)
                    {
                        case tgParameterDirection.InputOutput:
                            param.Direction = ParameterDirection.InputOutput;
                            break;

                        case tgParameterDirection.Output:
                            param.Direction = ParameterDirection.Output;
                            param.DbType = esParam.DbType;
                            param.Size = esParam.Size;
                            break;

                        case tgParameterDirection.ReturnValue:
                            param.Direction = ParameterDirection.ReturnValue;
                            break;

                        // The default is ParameterDirection.Input;
                    }
                }
            }
        }

        /// <summary>
        /// Simplify the creation of a VistaDB command object by allowing
        /// a CommandType and Command Text to be provided
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  VistaDBCommand command = CreateCommand(conn, CommandType.Text, "Select * from Customers");
        /// </remarks>
        /// <param name="connection">A valid VistaDBConnection object</param>
        /// <param name="commandType">CommandType (TableDirect, Text)</param>
        /// <param name="commandText">CommandText</param>
        /// <returns>A valid VistaDBCommand object</returns>
        public static VistaDBCommand CreateCommand(VistaDBConnection connection, CommandType commandType, string commandText )
        {
            if( connection == null ) throw new ArgumentNullException( "connection" );

            if( commandType == CommandType.StoredProcedure ) throw new ArgumentException("Stored Procedures are not supported.");

            // If we receive parameter values, we need to figure out where they go
            if ((commandText == null) && (commandText.Length<= 0)) throw new ArgumentNullException( "Command Text" );

            // Create a VistaDBCommand
            VistaDBCommand cmd = new VistaDBCommand(commandText, connection );
            cmd.CommandType = CommandType.Text ;

            return cmd;
        }

        protected static string BuildQuery(esDataRequest request, esDynamicQuerySerializable query, VistaDBCommand cmd, ref int pindex)
        {
            IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;

            string select = GetSelectStatement(request, query, cmd, ref pindex);
            string from = GetFromStatement(request, query, cmd, ref pindex);
            string join = GetJoinStatement(request, query, cmd, ref pindex);
            string where = GetComparisonStatement(request, query, iQuery.InternalWhereItems, " WHERE ", cmd, ref pindex);
            string groupBy = GetGroupByStatement(request, query, cmd, ref pindex);
            string having = GetComparisonStatement(request, query, iQuery.InternalHavingItems, " HAVING ", cmd, ref pindex);
            string orderBy = GetOrderByStatement(request, query, cmd, ref pindex);
            string setOperation = GetSetOperationStatement(request, query, cmd, ref pindex);

            string sql = String.Empty;

            sql += "SELECT " + select + " FROM " + from + join + where + setOperation + groupBy + having + orderBy;

            return sql;
        }

        private static tgDataResponse LoadDataTableFromStoredProcedure(tgDataRequest request)
        {
            tgDataResponse response = new tgDataResponse();
            VistaDBCommand cmd = null;

            try
            {
                DataTable dataTable = new DataTable(request.ProviderMetadata.Destination);

                cmd = new VistaDBCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = Shared.CreateFullName(request);
                if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
                if (request.Parameters != null) Shared.AddParameters(cmd, request);

                VistaDBDataAdapter da = new VistaDBDataAdapter();
                da.SelectCommand = cmd;

                try
                {
                    tgTransactionScope.Enlist(da.SelectCommand, request.ConnectionString, CreateIDbConnectionDelegate);

                    #region Profiling

                    if (sTraceHandler != null)
                    {
                        using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "LoadFromStoredProcedure", System.Environment.StackTrace))
                        {
                            try
                            {
                                da.Fill(dataTable);
                            }
                            catch (Exception ex)
                            {
                                esTrace.Exception = ex.Message;
                                throw;
                            }
                        }
                    }
                    else

                    #endregion Profiling

                    {
                        da.Fill(dataTable);
                    }
                }
                finally
                {
                    tgTransactionScope.DeEnlist(da.SelectCommand);
                }

                response.Table = dataTable;

                if (request.Parameters != null)
                {
                    Shared.GatherReturnParameters(cmd, request, response);
                }
            }
            catch (Exception)
            {
                CleanupCommand(cmd);
                throw;
            }
            finally
            {
            }

            return response;
        }

        internal override IDbCommand _LoadFromRawSql(string rawSql, params object[]	parameters)
        {
            int i = 0;
            string token  = "";
            string sIndex = "";
            string param  = "";

            VistaDBCommand cmd = new VistaDBCommand();

            foreach(object o in parameters)
            {
                sIndex = i.ToString();
                token = '{' + sIndex + '}';
                param = "@p" + sIndex;

                rawSql = rawSql.Replace(token, param);

                VistaDBParameter p = new VistaDBParameter(param, o);
                cmd.Parameters.Add(p);
                i++;
            }

            cmd.CommandText = rawSql;
            return cmd;
        }

        protected static string GetOrderByStatement(esDataRequest request, esDynamicQuerySerializable query, VistaDBCommand cmd, ref int pindex)
        {
            string sql = String.Empty;
            string comma = String.Empty;

            IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;

            if (iQuery.InternalOrderByItems != null)
            {
                sql += " ORDER BY ";

                foreach (esOrderByItem orderByItem in iQuery.InternalOrderByItems)
                {
                    bool literal = false;

                    sql += comma;

                    string columnName = orderByItem.Expression.Column.Name;

                    if (columnName != null && columnName[0] == '<')
                    {
                        sql += columnName.Substring(1, columnName.Length - 2);

                        if (orderByItem.Direction == esOrderByDirection.Unassigned)
                        {
                            literal = true; // They must provide the DESC/ASC in the literal string
                        }
                    }
                    else
                    {
                        sql += GetExpressionColumn(orderByItem.Expression, false, false);
                    }

                    if (!literal)
                    {
                        if (orderByItem.Direction == esOrderByDirection.Ascending)
                            sql += " ASC";
                        else
                            sql += " DESC";
                    }

                    comma = ",";
                }
            }

            return sql;
        }

		override protected IDbCommand _Load(string conjuction) 
		{
			bool hasColumn = false;
			bool selectAll = true;
			string query;

			query = "SELECT ";

			if( this._distinct) query += " DISTINCT ";
			if( this._top >= 0) query += " TOP " + this._top.ToString() + " ";

			if(this._resultColumns.Length > 0)
			{
				query += this._resultColumns;
				hasColumn = true;
				selectAll = false;
			}
	 
			if(this._countAll)
			{
				if(hasColumn)
				{
					query += ", ";
				}
				
				query += "COUNT(*)";

				if(this._countAllAlias != string.Empty)
				{
					// Need DBMS string delimiter here
					query += " AS [" + this._countAllAlias + "]";
				}
				
				hasColumn = true;
				selectAll = false;
			}

			if(_aggregateParameters != null && _aggregateParameters.Count > 0)
			{
				bool isFirst = true;
				
				if(hasColumn)
				{
					query += ", ";
				}
				
				AggregateParameter wItem;
	
				foreach(object obj in _aggregateParameters)
				{
					wItem = obj as AggregateParameter;
	
					if(wItem.IsDirty)
					{
						if(isFirst)
						{
							query += GetAggregate(wItem, true);
							isFirst = false;
						}
						else
						{
							query += ", " + GetAggregate(wItem, true);
						}
					}
				}
				
				selectAll = false;
			}
			
			if(selectAll)
			{
				query += "*";
			}

			query += " FROM [" + this._entity.QuerySource + "]";

			VistaDBCommand cmd = new VistaDBCommand();

			if(_whereParameters != null && _whereParameters.Count > 0)
			{
				query += " WHERE ";

				bool first = true;

				bool requiresParam;

				WhereParameter wItem;
				bool skipConjuction = false;

				string paramName;
				string columnName;

				foreach(object obj in _whereParameters)
				{
					// Maybe we injected text or a WhereParameter
					if(obj.GetType().ToString() == "System.String")
					{
						string text = obj as string;
						query += text;

//.........这里部分代码省略.........

        protected static string GetComparisonStatement(esDataRequest request, esDynamicQuerySerializable query, List<esComparison> items, string prefix, VistaDBCommand cmd, ref int pindex)
        {
            string sql = String.Empty;
            string comma = String.Empty;

            IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;

            //=======================================
            // WHERE
            //=======================================
            if (items != null)
            {
                sql += prefix;

                string compareTo = String.Empty;
                foreach (esComparison comparisonItem in items)
                {
                    esComparison.esComparisonData comparisonData = (esComparison.esComparisonData)comparisonItem;
                    esDynamicQuerySerializable subQuery = null;

                    bool requiresParam = true;
                    bool needsStringParameter = false;

                    if (comparisonData.IsParenthesis)
                    {
                        if (comparisonData.Parenthesis == esParenthesis.Open)
                            sql += "(";
                        else
                            sql += ")";

                        continue;
                    }

                    if (comparisonData.IsConjunction)
                    {
                        switch (comparisonData.Conjunction)
                        {
                            case esConjunction.And: sql += " AND "; break;
                            case esConjunction.Or: sql += " OR "; break;
                            case esConjunction.AndNot: sql += " AND NOT "; break;
                            case esConjunction.OrNot: sql += " OR NOT "; break;
                        }
                        continue;
                    }

                    Dictionary<string, VistaDBParameter> types = null;
                    if (comparisonData.Column.Query != null)
                    {
                        IDynamicQuerySerializableInternal iLocalQuery = comparisonData.Column.Query as IDynamicQuerySerializableInternal;
                        types = Cache.GetParameters(iLocalQuery.DataID, (esProviderSpecificMetadata)iLocalQuery.ProviderMetadata, (esColumnMetadataCollection)iLocalQuery.Columns);
                    }

                    if (comparisonData.IsLiteral)
                    {
                        if (comparisonData.Column.Name[0] == '<')
                        {
                            sql += comparisonData.Column.Name.Substring(1, comparisonData.Column.Name.Length - 2);
                        }
                        else
                        {
                            sql += comparisonData.Column.Name;
                        }
                        continue;
                    }

                    if (comparisonData.ComparisonColumn.Name == null)
                    {
                        subQuery = comparisonData.Value as esDynamicQuerySerializable;

                        if (subQuery == null)
                        {
                            if (comparisonData.Column.Name != null)
                            {
                                IDynamicQuerySerializableInternal iColQuery = comparisonData.Column.Query as IDynamicQuerySerializableInternal;
                                esColumnMetadataCollection columns = (esColumnMetadataCollection)iColQuery.Columns;
                                compareTo = Delimiters.Param + columns[comparisonData.Column.Name].PropertyName + (++pindex).ToString();
                            }
                            else
                            {
                                compareTo = Delimiters.Param + "Expr" + (++pindex).ToString();
                            }
                        }
                        else
                        {
                            // It's a sub query
                            compareTo = GetSubquerySearchCondition(subQuery) + " (" + BuildQuery(request, subQuery, cmd, ref pindex) + ") ";
                            requiresParam = false;
                        }
                    }
                    else
                    {
                        compareTo = GetColumnName(comparisonData.ComparisonColumn);
                        requiresParam = false;
                    }

                    switch (comparisonData.Operand)
                    {
                        case esComparisonOperand.Exists:
                            sql += " EXISTS" + compareTo;
                            break;
//.........这里部分代码省略.........

		protected override IDbCommand GetInsertCommand()
		{
			VistaDBCommand cmd = new VistaDBCommand();
			cmd.CommandType = CommandType.Text;
			cmd.CommandText =
				@"INSERT INTO [Employees]
			(
				[LastName],
				[FirstName],
				[Title],
				[TitleOfCourtesy],
				[BirthDate],
				[HireDate],
				[Address],
				[City],
				[Region],
				[PostalCode],
				[Country],
				[HomePhone],
				[Extension],
				[Photo],
				[Notes],
				[ReportsTo]
			)
			VALUES
			(
				@LastName,
				@FirstName,
				@Title,
				@TitleOfCourtesy,
				@BirthDate,
				@HireDate,
				@Address,
				@City,
				@Region,
				@PostalCode,
				@Country,
				@HomePhone,
				@Extension,
				@Photo,
				@Notes,
				@ReportsTo
			)";

			CreateParameters(cmd);
			return cmd;
		}

		protected override IDbCommand GetDeleteCommand()
		{
			VistaDBCommand cmd = new VistaDBCommand();
			cmd.CommandType = CommandType.Text;
			cmd.CommandText =
				@"DELETE FROM [Employees] 
			WHERE
				[EmployeeID][email protected]";

	
			VistaDBParameter p;
			p = cmd.Parameters.Add(Parameters.EmployeeID);
			p.SourceColumn = ColumnNames.EmployeeID;
			p.SourceVersion = DataRowVersion.Current;

  
			return cmd;
		}

        private static tgDataResponse LoadManyToMany(tgDataRequest request)
        {
            tgDataResponse response = new tgDataResponse();
            VistaDBCommand cmd = null;

            try
            {
                DataTable dataTable = new DataTable(request.ProviderMetadata.Destination);

                cmd = new VistaDBCommand();
                cmd.CommandType = CommandType.Text;
                if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;

                string mmQuery = request.QueryText;

                string[] sections = mmQuery.Split('|');
                string[] tables = sections[0].Split(',');
                string[] columns = sections[1].Split(',');

                // We build the query, we don't use Delimiters to avoid tons of extra concatentation
                string sql = "SELECT * FROM [" + tables[0];
                sql += "] JOIN [" + tables[1] + "] ON [" + tables[0] + "].[" + columns[0] + "] = [";
                sql += tables[1] + "].[" + columns[1];
                sql += "] WHERE [" + tables[1] + "].[" + sections[2] + "] = @";

                if (request.Parameters != null)
                {
                    foreach (tgParameter esParam in request.Parameters)
                    {
                        sql += esParam.Name;
                    }

                    Shared.AddParameters(cmd, request);
                }

                VistaDBDataAdapter da = new VistaDBDataAdapter();
                cmd.CommandText = sql;

                da.SelectCommand = cmd;

                try
                {
                    tgTransactionScope.Enlist(da.SelectCommand, request.ConnectionString, CreateIDbConnectionDelegate);

                    #region Profiling

                    if (sTraceHandler != null)
                    {
                        using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "LoadManyToMany", System.Environment.StackTrace))
                        {
                            try
                            {
                                da.Fill(dataTable);
                            }
                            catch (Exception ex)
                            {
                                esTrace.Exception = ex.Message;
                                throw;
                            }
                        }
                    }
                    else

                    #endregion Profiling

                    {
                        da.Fill(dataTable);
                    }
                }
                finally
                {
                    tgTransactionScope.DeEnlist(da.SelectCommand);
                }

                response.Table = dataTable;
            }
            catch (Exception)
            {
                CleanupCommand(cmd);
                throw;
            }
            finally
            {
            }

            return response;
        }

		DataTable IMyMetaPlugin.GetViews(string database)
		{
			DataTable metaData = new DataTable();
			//IVistaDBDatabase db = null;

			try
			{
				metaData = context.CreateViewsDataTable();

				using (VistaDBConnection conn = new VistaDBConnection())
				{
					conn.ConnectionString = context.ConnectionString;
					conn.Open();

					using (VistaDBCommand cmd = new VistaDBCommand("SELECT * FROM GetViews()", conn))
					{
						using (VistaDBDataAdapter da = new VistaDBDataAdapter(cmd))
						{
							DataTable views = new DataTable();
							da.Fill(views);

							foreach(DataRow vistaRow in views.Rows)
							{
								DataRow row = metaData.NewRow();
								metaData.Rows.Add(row);

								row["TABLE_NAME"]   = vistaRow["VIEW_NAME"];
								row["DESCRIPTION"]  = vistaRow["DESCRIPTION"];
								row["VIEW_TEXT"]    = vistaRow["VIEW_DEFINITION"];
								row["IS_UPDATABLE"] = vistaRow["IS_UPDATABLE"];
							}
						}						 
					}
				}
			}
			catch{}

			return metaData;
		}