C# Data.IDbConnection类代码示例

        public override System.Data.IDbConnection GetConnection()
        {
            if (Connection == null)
                Connection = base.GetConnection();

            return Connection;
        }

              public Basiclist(System.Data.IDbConnection connection, string volumename)
              {
                m_connection = connection;
                m_volumename = volumename;
                m_transaction = m_connection.BeginTransaction();
                var tablename = TABLE_PREFIX + "-" + Library.Utility.Utility.ByteArrayAsHexString(Guid.NewGuid().ToByteArray());

                using(var cmd = m_connection.CreateCommand())
                {
                    cmd.Transaction = m_transaction;
                    cmd.ExecuteNonQuery(string.Format(@"CREATE TEMPORARY TABLE ""{0}"" {1}", tablename, TABLEFORMAT));
                    m_tablename = tablename;
                }
                
                m_insertCommand = m_connection.CreateCommand();
                m_insertCommand.Transaction = m_transaction;
                m_insertCommand.CommandText = string.Format(@"INSERT INTO ""{0}"" {1}", m_tablename, INSERTCOMMAND);
                m_insertCommand.AddParameters(INSERTARGUMENTS);
            }

 public DBConnection(IDBProvider dbProvider, string ConnectionString)
 {
     this.CommandTimeout = 30;
     this._dbProvider = dbProvider;
     _conn = (System.Data.IDbConnection)_dbProvider.CreateConnection(ConnectionString);
 }

 /// <summary>
 /// ��ʼ������
 /// </summary>
 /// <param name="conn">���ݿ����Ӷ��󣬸ö�������Ѿ���</param>
 public MyORMFramework( System.Data.IDbConnection conn )
 {
     myConnection = conn ;
 }

            public FilesAndMetadata(System.Data.IDbConnection connection, string filetablename, string blocktablename, long blocksize, BlockVolumeReader curvolume)
            {
                m_filetablename = filetablename;
                m_blocktablename = blocktablename;
                m_blocksize = blocksize;
                m_connection = connection;

                using (var c = m_connection.CreateCommand())
                {
                    m_tmptable = "VolumeFiles-" + Library.Utility.Utility.ByteArrayAsHexString(Guid.NewGuid().ToByteArray());
                    c.CommandText = string.Format(@"CREATE TEMPORARY TABLE ""{0}"" ( ""Hash"" TEXT NOT NULL, ""Size"" INTEGER NOT NULL )", m_tmptable);
                    c.ExecuteNonQuery();


                    c.CommandText = string.Format(@"INSERT INTO ""{0}"" (""Hash"", ""Size"") VALUES (?,?)", m_tmptable);
                    c.AddParameters(2);
                    foreach (var s in curvolume.Blocks)
                    {
                        c.SetParameterValue(0, s.Key);
                        c.SetParameterValue(1, s.Value);
                        c.ExecuteNonQuery();
                    }

                    // The index _HashSizeIndex is not needed anymore. Index on "Blocks-..." is used on Join in GetMissingBlocks

                }
            }

        public bool TestConnection(string query)
        {
            try
            {
                IConn = this.GetConnection();

                IConn.Open();

                bool result = false;

                System.Data.IDbCommand cmd = IConn.CreateCommand();

                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = query;

                System.Data.IDataReader _reader = cmd.ExecuteReader();

                result = (_reader.FieldCount > 0);

                _reader.Close();
                IConn.Close();

                return result;

            }
            catch (Exception ex) {
                Logger.Error(ex.Message);
            }
            return false;
        }

            public FileSets(LocalListDatabase owner, DateTime time, long[] versions)
            {
                m_connection = owner.m_connection;
                m_filesets = owner.FilesetTimes.ToArray();
                m_tablename = "Filesets-" + Library.Utility.Utility.ByteArrayAsHexString(Guid.NewGuid().ToByteArray());
				var tmp = owner.GetFilelistWhereClause(time, versions, m_filesets);
				string query = tmp.Item1;
				var args = tmp.Item2;
                
                using(var cmd = m_connection.CreateCommand())
                    cmd.ExecuteNonQuery(string.Format(@"CREATE TEMPORARY TABLE ""{0}"" AS SELECT DISTINCT ""ID"" AS ""FilesetID"", ""Timestamp"" AS ""Timestamp"" FROM ""Fileset"" " + query, m_tablename), args);
            }

        /*
         * 使用微软提供的操作Offfice的库DocumentFormat.OpenXml
         * ClosedXML在此基础上进行了封装，简化操作。
         *
         * */
        static void Main(string[] args)
        {
            HZ_Conn = new SqlConnection(HZ_ConnectionString);
            HZ_Conn.Open();

            DC_Conn = new SqlConnection(DC_ConnectionString);
            DC_Conn.Open();

            var workbook = new XLWorkbook();
            var worksheet = workbook.Worksheets.Add("Sheet1");

            var url = "http://webapp.hc.ihaozhuo.com/SMSPromotion.html#/{0}/{1}/{2}";
            //http://webapp.hc.ihaozhuo.com/SMSPromotion.html#/{task}/{mobile}/{org}

            var table = "bjbr008";
            var deptCode = "bjbr008";

            //黎波，刘述正，刘倩倩,
            //var mobiles = new string[] { "15900860546", "17783055953","15601815186"};
            var mobiles = HZ_Conn.Query<string>("select  distinct telephone from " + table);//distinct

            var shortLinkProxy = SortLinkServerProxy.ShareInstance();

            var time = DateTime.Now;
            var sms = (from c in mobiles
                       where !string.IsNullOrEmpty(c) && Regex.Match(c, "1[2|3|5|7|8|][0-9]{9}").Success
                       select new
                       {
                           mobile = c,
                           sms = shortLinkProxy.getSortLink(string.Format(url, 1, c, deptCode)) + " "
                       }).ToList();

            Trace.WriteLine(string.Format("cost:{0}", DateTime.Now.Subtract(time).TotalSeconds));

            var rowStart = 2;
            var columnStart = 1;

            foreach (var item in sms)
            {
                worksheet.Cell(rowStart, columnStart).Value = item.mobile;
                worksheet.Cell(rowStart, columnStart + 1).Value = "【新乡第一人民医院】温馨提示,您的体检报告已完成，查看您的健康状况及阳性指标，永久保存报告详情请点击";
                worksheet.Cell(rowStart, columnStart + 2).Value = item.sms + " 退订回N";
                worksheet.Cell(rowStart, columnStart + 3).Value = item.sms;

                rowStart++;
            }

            workbook.SaveAs("HelloWorld.xlsx");
        }

 private int Getlistcountattr(iSqlConnection iConn, string categoryid, int attrid, ListOptions options)
 {
     try
     {
         int numResults = 0;
         string SQL = SQL_COUNT.Replace(Queryparam.Varstring.VAR_TABLENAME, TABLENAME);
         SQL += " AND A.status<>" + (int)CConstants.State.Status.Waitactive;
         SQL += " AND A.status<>" + (int)CConstants.State.Status.Disabled;
         SQL += options.Markas == (int)CConstants.State.MarkAs.None ? "" : " AND A.markas=" + options.Markas;
         SQL += CFunctions.IsNullOrEmpty(categoryid) ? "" : " AND A.id IN(SELECT iid FROM " + LANG + CConstants.TBDBPREFIX + "itemcategory WHERE categoryid IN(" + categoryid + ") AND belongto=" + Webcmm.Id.Video + ")";
         SQL += attrid == 0 ? "" : " AND A.id IN(SELECT iid FROM " + LANG + CConstants.TBDBPREFIX + "itemcategoryattr WHERE categoryid IN(" + attrid + ") AND belongto=" + Webcmm.Id.Video + ")";
         using (iSqlDataReader dar = HELPER.executeReader(iConn, SQL))
         {
             if (dar.Read())
             {
                 numResults = dar.IsDBNull(0) ? 0 : dar.GetInt32(0);
             }
         }
         return numResults;
     }
     catch
     {
         return 0;
     }
 }

 public int GetDictCount(iSqlConnection iConn, string categoryid)
 {
     try
     {
         int numResults = 0;
         string SQL =
             @"select COUNT(*) as sl from vndd_itemcategory a left join
     vndd_news b on a.iid = b.id where b.status <> 3 and a.categoryid = " + categoryid;
         using (iSqlDataReader dar = HELPER.executeReader(iConn, SQL))
         {
             if (dar.Read())
             {
                 numResults = dar.IsDBNull(0) ? 0 : dar.GetInt32(0);
             }
         }
         return numResults;
     }
     catch
     {
         return 0;
     }
 }

        private int Wcmm_Reportcount(iSqlConnection iConn, SearchInfo isearch, ListOptions options)
        {
            try
            {
                int numResults = 0;
                string SQL = SQL_COUNT.Replace(Queryparam.Varstring.VAR_TABLENAME, TABLENAME);
                SQL += CFunctions.IsNullOrEmpty(isearch.Setof_Category) ? "" : " AND A.cid IN(" + isearch.Setof_Category + ")";
                SQL += string.IsNullOrEmpty(isearch.Searchquery) ? "" : " AND (" + isearch.Searchquery + ")";
                SQL += " AND (A.timeupdate BETWEEN '" + isearch.Datefr + "' AND '" + isearch.Dateto + "')";

                using (iSqlDataReader dar = HELPER.executeReader(iConn, SQL))
                {
                    if (dar.Read())
                    {
                        numResults = dar.IsDBNull(0) ? 0 : dar.GetInt32(0);
                    }
                }
                return numResults;
            }
            catch
            {
                return 0;
            }
        }

        private int Wcmm_Getlistcount(iSqlConnection iConn, int memberid, ListOptions options)
        {
            try
            {
                int numResults = 0;
                string SQL = SQL_COUNT.Replace(Queryparam.Varstring.VAR_TABLENAME, TABLENAME);
                SQL += memberid == 0 ? "" : " AND A.memberid=" + memberid;

                using (iSqlDataReader dar = HELPER.executeReader(iConn, SQL))
                {
                    if (dar.Read())
                    {
                        numResults = dar.IsDBNull(0) ? 0 : dar.GetInt32(0);
                    }
                }
                return numResults;
            }
            catch
            {
                return 0;
            }
        }

        private int Searchcount(iSqlConnection iConn, int belongto, string categoryid, string Searchquery)
        {
            try
            {
                int numResults = 0;
                string SQL = SQL_COUNT_SEARCH.Replace(Queryparam.Varstring.VAR_TABLENAME, TABLENAME);
                SQL += " AND A.status<>" + (int)CConstants.State.Status.Waitactive;
                SQL += " AND A.status<>" + (int)CConstants.State.Status.Disabled;
                SQL += CFunctions.IsNullOrEmpty(categoryid) || categoryid == "0" ? "" : " AND A.id IN(SELECT iid FROM " + LANG + CConstants.TBDBPREFIX + "itemcategory WHERE categoryid IN(" + categoryid + ") AND belongto=" + belongto + ")";
                SQL += string.IsNullOrEmpty(Searchquery) ? "" : " AND (" + Searchquery + ")";

                using (iSqlDataReader dar = HELPER.executeReader(iConn, SQL))
                {
                    if (dar.Read())
                    {
                        numResults = dar.IsDBNull(0) ? 0 : dar.GetInt32(0);
                    }
                }
                return numResults;
            }
            catch
            {
                return 0;
            }
        }

        private int Wcmm_Searchcount(iSqlConnection iConn, string categoryid, string Searchquery, ListOptions options)
        {
            try
            {
                int numResults = 0;
                string SQL = SQL_COUNT.Replace(Queryparam.Varstring.VAR_TABLENAME, TABLENAME);
                SQL += CFunctions.Expression_GetPermit(options.GetAll, options.Username);
                SQL += CFunctions.IsNullOrEmpty(categoryid) ? "" : " AND A.id IN(SELECT iid FROM " + LANG + CConstants.TBDBPREFIX + "itemcategory WHERE categoryid IN(" + categoryid + ") AND belongto=" + Webcmm.Id.Video + ")";
                SQL += string.IsNullOrEmpty(Searchquery) ? "" : " AND (" + Searchquery + ")";

                using (iSqlDataReader dar = HELPER.executeReader(iConn, SQL))
                {
                    if (dar.Read())
                    {
                        numResults = dar.IsDBNull(0) ? 0 : dar.GetInt32(0);
                    }
                }
                return numResults;
            }
            catch
            {
                return 0;
            }
        }

        private int Getlistcount(iSqlConnection iConn, int cid, int pid)
        {
            try
            {
                int numResults = 0;
                string SQL = SQL_COUNT.Replace(Queryparam.Varstring.VAR_TABLENAME, TABLENAME);
                SQL += " AND A.status<>" + (int)CConstants.State.Status.Waitactive;
                SQL += " AND A.status<>" + (int)CConstants.State.Status.Disabled;
                SQL += cid == 0 ? "" : " AND A.cid=" + cid;
                SQL += pid == -1 ? "" : " AND A.pid=" + pid;

                using (iSqlDataReader dar = HELPER.executeReader(iConn, SQL))
                {
                    if (dar.Read())
                    {
                        numResults = dar.IsDBNull(0) ? 0 : dar.GetInt32(0);
                    }
                }
                return numResults;
            }
            catch
            {
                return 0;
            }
        }