示例#1
0
        /// <summary>
        /// 复杂检索数据库数据,返回泛型集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="complexQuery"></param>
        /// <returns></returns>
        public IEnumerable <T> Query <T>(ComplexQuery complexQuery)
        {
            IList <T> result = new List <T>();

            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                SqlCommand cmd = conn.CreateCommand();
                ComplexQueryTranslator.TranslateIntoComplexQuery(complexQuery, cmd);

                try
                {
                    conn.Open();
                    SqlDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        result.Add(Mapper.DynamicMap <T>(reader));
                    }
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Source + ":" + ex.Message);
                }
            }
            return(result);
        }
示例#2
0
        /// <summary>
        /// 查询一张表中指定字段的数据
        /// </summary>
        /// <param name="complexQuery"></param>
        /// <returns></returns>
        public DataTable Query(ComplexQuery complexQuery)
        {
            DataTable result = new DataTable();

            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                SqlCommand cmd = conn.CreateCommand();
                ComplexQueryTranslator.TranslateIntoComplexQuery(complexQuery, cmd);

                try
                {
                    //conn.Open();
                    SqlDataAdapter ad = new SqlDataAdapter(cmd);
                    ad.Fill(result);
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Source + ":" + ex.Message);
                }
            }
            return(result);
        }
示例#3
0
        static void Main(string[] args)
        {
            string conn = "Data Source=QH-20140814XCYI;Initial Catalog=Test;Integrated Security=True";
            SqlServerDataFactory dataFactory = new SqlServerDataFactory(conn);
            DataTable            dttest      = new DataTable();
            DataColumn           dc          = new DataColumn("d", typeof(string));

            dttest.Columns.Add(dc);

            DataColumn dc1 = new DataColumn("c", typeof(int));

            dttest.Columns.Add(dc1);

            DataRow dr = dttest.NewRow();

            dr["c"] = 2;
            dr["d"] = "123ss";
            dttest.Rows.Add(dr);
            DataRow dr1 = dttest.NewRow();

            dr1["c"] = 3;
            dr1["d"] = "123ss";
            dttest.Rows.Add(dr1);
            //dataFactory.Insert("B", dttest);

            string[] key = { "c" };
            dataFactory.Update("B", dttest, key);
            Console.ReadKey();

            //////////////////////////////////////delete
            Delete delete = new Delete("tableName");

            delete.AddCriterions("a", "zhangxin", "1", CriteriaOperator.MoreThan);
            delete.AddCriterions("a", "zhangdi", 2, CriteriaOperator.LessThan);
            delete.AddCriterions("b", "2", CriteriaOperator.Equal);
            delete.AddSqlOperator(SqlOperator.OR);

            SqlCommand deleteCmd = new SqlCommand();

            DeleteTranslator.TranslateIntoDelete(delete, deleteCmd);
            Console.WriteLine(deleteCmd.CommandText);
            Console.WriteLine(deleteCmd.Parameters.Count);
            //Console.ReadKey();
            //////////////////////////////////////////////////

            Test test = new Test
            {
                a = "1",
                b = "2",
                c = "3"
            };
            Insert <Test> insert = new Insert <Test>("tableName", test);

            insert.AddExcludeField("a");
            SqlCommand insertCmd = new SqlCommand();

            SaveTranslator.TranslateIntoInsert <Test>(insert, insertCmd);
            Console.WriteLine(insertCmd.CommandText);
            Console.WriteLine(insertCmd.Parameters.Count);
            //Console.ReadKey();
            ////////////////////////////////////////////////////////////

            Test test1 = new Test
            {
                a = "1",
                b = "2",
                c = "3"
            };
            Update <Test> update = new Update <Test>("taleName", test1);

            update.AddCriterion("a", "a", "1", CriteriaOperator.Equal);
            update.AddCriterion("b", "b", "2", CriteriaOperator.Equal);
            update.AddExcludeField("c");
            update.AddSqlOperator(SqlOperator.AND);
            SqlCommand updateCmd = new SqlCommand();

            SaveTranslator.TranslateIntoUpdate <Test>(update, updateCmd);
            Console.WriteLine(updateCmd.CommandText);
            Console.WriteLine(updateCmd.Parameters.Count);
            //Console.ReadKey();

            ////////////////////////////////////////////////////////////

            Query query = new Query("tableName");

            query.AddCriterion("a.b", "1", CriteriaOperator.Equal);
            //query.AddCriterion("b", "b", "2", CriteriaOperator.Equal);
            query.AddCriterion("c", "____", CriteriaOperator.Like);
            query.AddOrderByClause(new OrderByClause("a", false));
            query.AddOrderByClause(new OrderByClause("b", true));
            //query.AddSqlOperator(SqlOperator.AND);
            SqlCommand queryCmd = new SqlCommand();

            QueryTranslator.TranslateIntoSelect(query, queryCmd);
            Console.WriteLine(queryCmd.CommandText);
            Console.WriteLine(queryCmd.Parameters.Count);
            //Console.ReadKey();

            //////////////////////////////////////////////////////////////

            ComplexQuery cmquery = new ComplexQuery();

            IList <NeedField> list      = new List <NeedField>();
            NeedField         needField = new NeedField
            {
                TableName    = "tableName1",
                FieldName    = "a",
                VariableName = ""
            };

            list.Add(needField);
            cmquery.NeedFields = list;
            cmquery.AddNeedField("tableName2", "b");
            cmquery.AddNeedField("tableName3", "c", "test");
            IDictionary <string, string> testjoin = new Dictionary <string, string>();

            testjoin.Add("tableName1", "c1");
            testjoin.Add("tableName2", "c2");
            testjoin.Add("tableName3", "c3");


            //cmquery.AddJoinCriterion("assss", JoinType.FULL_JOIN);
            cmquery.AddJoinCriterion(testjoin, JoinType.INNER_JOIN);



            //cmquery.IsDictinct = true;
            //cmquery.TopNumber = 9;
            cmquery.AddCriterion("a", "a", "1", CriteriaOperator.Equal);
            cmquery.AddCriterion("b", "b", "2", CriteriaOperator.Equal);
            cmquery.AddCriterion("c", "c", "3", CriteriaOperator.Like);
            List <OrderByClause> orders = new List <OrderByClause>();

            orders.Add(new OrderByClause("a", true));
            orders.Add(new OrderByClause("b", false));
            cmquery.OrderByClauses = orders;
            SqlCommand cmqueryCmd = new SqlCommand();

            ComplexQueryTranslator.TranslateIntoComplexQuery(cmquery, cmqueryCmd);
            Console.WriteLine(cmqueryCmd.CommandText);
            Console.WriteLine(cmqueryCmd.Parameters.Count);
            //Console.ReadKey();


            ///////////////////////////////////////////////////////////
            ComplexQuery testquery = new ComplexQuery();

            Console.WriteLine(testquery.TopNumber);
            Console.WriteLine(DateTime.Today);
            Console.WriteLine(DateTime.Now.AddDays(-1).Date);
            //Console.ReadKey();

            ///////////////////////////////////////////////////////

            //Query testQuery = new Query("B");
            //SqlServerDataFactory dataFactory = new SqlServerDataFactory("Data Source=QH-20140814XCYI;Initial Catalog=Test;Integrated Security=True");
            //IEnumerable<BModle> result = dataFactory.Query<BModle>(testQuery);
            //foreach (var item in result)
            //{
            //    //BModle a = (BModle)item;
            //    Console.WriteLine(item.d);
            //    Console.WriteLine(item.c);
            //}
            //Console.WriteLine(result.Count());
            //Console.ReadKey();

            JoinCriterion joinCri = new JoinCriterion();

            Console.WriteLine("++" + joinCri.DefaultJoinFieldName + "++");
            Console.ReadKey();

            Criterion testcri = new Criterion("name", "", 1, CriteriaOperator.Equal);

            Console.WriteLine(testcri.ParameterName);
            Console.ReadKey();

            //SqlServerDataFactory dataFactory = new SqlServerDataFactory("");
            //SqlParameter a = new SqlParameter();
            //SqlParameter b = new SqlParameter("", typeof(int));
            //dataFactory.Query("", a, b);
            //dataFactory.Query("");
            //SqlParameter[] arry = { new SqlParameter(), new SqlParameter("", typeof(int)) };
            //dataFactory.Query("", arry);
        }