1月 29

2017-5-11 更新版

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using System.Data;
using System.IO;
using Dapper;

namespace Test.Dappere
{
    class Program
    {
        static string ConnectionString = "Server=127.0.0.1;Database="+DBNANME+";Uid=root;Pwd=root;CharSet=utf8;Allow Zero Datetime=True";
        static string SavePath = @"F:\1";

        static string DBNANME = "db_tp84";

        static void Main(string[] args)
        {

            var tables = GetTables(DBNANME);
            foreach (var table in tables)
            {
                TableName = table.TABLE_NAME.ToLower();

                ClassName = TableName.Replace("tb_", string.Empty).Replace("ecs_", string.Empty);
                ClassName = ClassName.Substring(0, 1).ToUpper() + ClassName.Substring(1);
                var xxx = GetTableDetails();
                DoTable(xxx);
            }
        }
        public static string TableName;
        public static string ClassName;




        public static void DoTable(List<dynamic> list)
        {

            {
                var codeEntities = GetEntitiesCode(list);
                string dic = Path.Combine(SavePath, @"Entity\");
                if (!Directory.Exists(dic)) Directory.CreateDirectory(dic);
                File.WriteAllText(dic + ClassName + "Info.cs", codeEntities, Encoding.UTF8);
            }

            {
                var codeDAL = GetDALCode(list);
                string dic = Path.Combine(SavePath, @"DAL\");
                if (!Directory.Exists(dic)) Directory.CreateDirectory(dic);
                File.WriteAllText(dic + ClassName + "DAL.cs", codeDAL, Encoding.UTF8);
            }
        }

        private static string GetDALCode(List<dynamic> list)
        {
            bool isExist = false;
            StringBuilder sb = new StringBuilder();

            sb.AppendFormat("#region -- Insert {0} --\r\n", TableName);
            sb.AppendFormat("public static bool Insert({0}Info info)\r\n", ClassName);
            sb.Append("{\r\n");
            sb.Append("using (IDbConnection connection = new MySqlConnection(Config.ConnectionString))\r\n");
            sb.Append("{\r\n");
            sb.AppendFormat("var result = connection.Execute(@\"insert `{0}`(\r\n", TableName);
            sb.AppendFormat("\t\t\t\t\t\t\t\t\t\t\t\t\t{0})values(\r\n", GetColumnStrings(list, "`","`"));
            sb.AppendFormat("\t\t\t\t\t\t\t\t\t\t\t\t\t{0})\",info);\r\n", GetColumnStrings(list, "?",""));
            sb.Append("return result > 0;\r\n");
            sb.Append("}\r\n");
            sb.Append("}\r\n");
            sb.Append("#endregion\r\n\r\n");




            sb.AppendFormat("#region -- Delete {0} --\r\n", TableName);
            sb.Append("public static bool Delete(");
            var keysList = list.FindAll(a => a.COLUMN_KEY == "PRI");
            for (int index = 0; index < keysList.Count; index++)
            {
                sb.AppendFormat("{0} {1}{2}", GetCsharpType(keysList[index].DATA_TYPE), keysList[index].COLUMN_NAME, index == keysList.Count - 1 ? "" : ",");
            }
            sb.Append(")\r\n");
            sb.Append("{\r\n");
            sb.Append("using (IDbConnection connection = new MySqlConnection(Config.ConnectionString))\r\n");
            sb.Append("{\r\n");
            sb.AppendFormat("var result = connection.Execute(@\"DELETE FROM `{0}` WHERE\r\n", TableName);
            for (int index = 0; index < keysList.Count; index++)
            {
                sb.AppendFormat("\t\t\t\t\t\t\t\t\t\t\t\t\t`{0}`=?{0}{1}\r\n", keysList[index].COLUMN_NAME, index == keysList.Count - 1 ? "" : ",");
            }
            sb.Append("\t\t\t\t\t\t\t\t\t\t\t\t\t\",new {");
            for (int index = 0; index < keysList.Count; index++)
            {
                sb.AppendFormat("{0}={0}{1}\r\n", keysList[index].COLUMN_NAME, index == keysList.Count - 1 ? "" : ",");
            }
            sb.Append("});\r\n");

            sb.Append("return result > 0;\r\n");
            sb.Append("}\r\n");
            sb.Append("}\r\n");
            sb.Append("#endregion\r\n\r\n");


            sb.AppendFormat("#region -- Update {0} --\r\n", TableName);
            sb.AppendFormat("public static bool Update({0}Info info)\r\n", ClassName);
            sb.Append("{\r\n");
            sb.Append("using (IDbConnection connection = new MySqlConnection(Config.ConnectionString))\r\n");
            sb.Append("{\r\n");
            sb.AppendFormat("var result = connection.Execute(@\"UPDATE `{0}` SET\r\n", TableName);
            var updateList = list.FindAll(a => a.COLUMN_KEY != "PRI");
            for (int index = 0; index < updateList.Count; index++)
            {
                sb.AppendFormat("\t\t\t\t\t\t\t\t\t\t\t\t\t\t`{0}`=?{0}{1}\r\n", updateList[index].COLUMN_NAME, index == updateList.Count - 1 ? "" : ",");
            }
            sb.Append("\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE\r\n");
            var whereList = list.FindAll(a => a.COLUMN_KEY == "PRI");
            for (int index = 0; index < whereList.Count; index++)
            {
                sb.AppendFormat("\t\t\t\t\t\t\t\t\t\t\t\t\t\t`{0}`=?{0}{1}\r\n", whereList[index].COLUMN_NAME, index == whereList.Count - 1 ? "" : " AND");
            }
            sb.Append("\t\t\t\t\t\t\t\t\t\t\t\t\t\t\",info);\r\n");
            sb.Append("return result > 0;\r\n");
            sb.Append("}\r\n");
            sb.Append("}\r\n");
            sb.Append("#endregion\r\n\r\n");
            

            sb.AppendFormat("#region -- GetModule {0} --\r\n", TableName);
            sb.AppendFormat("public static  {0}Info  Get{0}Info(", ClassName);
            for (int index = 0; index < keysList.Count; index++)
            {
                sb.AppendFormat("{0} {1}{2}", GetCsharpType(keysList[index].DATA_TYPE), keysList[index].COLUMN_NAME, index == keysList.Count - 1 ? "" : ",");
            }
            sb.Append(")\r\n");
            sb.Append("{\r\n");
            sb.Append("using (IDbConnection connection = new MySqlConnection(Config.ConnectionString))\r\n");
            sb.Append("{\r\n");
            sb.AppendFormat("var info= connection.Query<{0}Info>(@\"select \r\n", ClassName);
            for (int index = 0; index < list.Count; index++)
            {
                sb.AppendFormat("\t\t\t\t\t\t\t\t\t\t\t\t\t\t`{0}`{1}\r\n", list[index].COLUMN_NAME, index == list.Count - 1 ? "" : ",");
            }
            sb.AppendFormat("\t\t\t\t\t\t\t\t\t\t\t\t\tFROM `{0}`\r\n", TableName);
            sb.Append("\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE\r\n");
            whereList = list.FindAll(a => a.COLUMN_KEY == "PRI");
            for (int index = 0; index < whereList.Count; index++)
            {
                sb.AppendFormat("\t\t\t\t\t\t\t\t\t\t\t\t\t\t`{0}`=?{0}{1}\r\n", whereList[index].COLUMN_NAME, index == whereList.Count - 1 ? "" : " AND ");
            }
            sb.Append("\t\t\t\t\t\t\t\t\t\t\t\t\t\",new {");
            for (int index = 0; index < keysList.Count; index++)
            {
                sb.AppendFormat("{0}={0}{1}", keysList[index].COLUMN_NAME, index == keysList.Count - 1 ? "" : ",");
            }
            sb.AppendFormat("}}).SingleOrDefault<{0}Info>();\r\n", ClassName);
            sb.Append("return info;\r\n");
            sb.Append("}\r\n");
            sb.Append("}\r\n");
            sb.Append("#endregion\r\n\r\n");






            sb.AppendFormat("#region -- GetList {0} --\r\n", TableName);
            sb.AppendFormat("public static List<{0}Info> Get{0}Infos(int pageIndex,int pageSize", ClassName);
            for (int index = 0; index < keysList.Count; index++)
            {
                sb.AppendFormat(",{0} {1}", GetCsharpType(keysList[index].DATA_TYPE), keysList[index].COLUMN_NAME);
            }
            sb.Append(")\r\n");

            sb.Append("{\r\n");
            sb.Append("using (IDbConnection connection = new MySqlConnection(Config.ConnectionString))\r\n");
            sb.Append("{\r\n");
            sb.AppendFormat("var list= connection.Query<{0}Info>(@\"select \r\n", ClassName);
            for (int index = 0; index < list.Count; index++)
            {
                sb.AppendFormat("\t\t\t\t\t\t\t\t\t\t\t\t\t\t`{0}`{1}\r\n", list[index].COLUMN_NAME, index == list.Count - 1 ? "" : ",");
            }
            sb.AppendFormat("\t\t\t\t\t\t\t\t\t\t\t\t\tFROM `{0}`\r\n", TableName);
            sb.Append("\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE\r\n");
            whereList = list.FindAll(a => a.COLUMN_KEY == "PRI");
            for (int index = 0; index < whereList.Count; index++)
            {
                sb.AppendFormat("\t\t\t\t\t\t\t\t\t\t\t\t\t\t`{0}`=?{0}{1}\r\n", whereList[index].COLUMN_NAME, index == whereList.Count - 1 ? "" : " AND ");
            }
            sb.Append("\t\t\t\t\t\t\t\t\t\t\t\t\tlimit ?start,?size\r\n");
            sb.Append("\t\t\t\t\t\t\t\t\t\t\t\t\t\",new {start = (pageIndex - 1) * pageSize, size = pageSize");
            for (int index = 0; index < keysList.Count; index++)
            {
                sb.AppendFormat(",{0}={0}", keysList[index].COLUMN_NAME);
            }
            sb.AppendFormat("}}).ToList<{0}Info>();\r\n", ClassName);
            sb.Append("return list;\r\n");
            sb.Append("}\r\n");
            sb.Append("}\r\n");
            sb.Append("#endregion\r\n\r\n");





            sb.AppendFormat("#region -- GetAllList {0} --\r\n", TableName);
            sb.AppendFormat("public static List<{0}Info> Get{0}Infos(", ClassName); 
            for (int index = 0; index < keysList.Count; index++)
            {
                sb.AppendFormat("{0} {1}{2}", GetCsharpType(keysList[index].DATA_TYPE), keysList[index].COLUMN_NAME,index==keysList.Count-1?"":",");
            }
            sb.Append(")\r\n");
            sb.Append("{\r\n");
            sb.Append("using (IDbConnection connection = new MySqlConnection(Config.ConnectionString))\r\n");
            sb.Append("{\r\n");
            sb.AppendFormat("var list= connection.Query<{0}Info>(@\"select \r\n", ClassName);
            for (int index = 0; index < list.Count; index++)
            {
                sb.AppendFormat("\t\t\t\t\t\t\t\t\t\t\t\t\t\t`{0}`{1}\r\n", list[index].COLUMN_NAME, index == list.Count - 1 ? "" : ",");
            }
            sb.AppendFormat("\t\t\t\t\t\t\t\t\t\t\t\t\tFROM `{0}`\r\n", TableName);
            sb.Append("\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE\r\n");
            whereList = list.FindAll(a => a.COLUMN_KEY == "PRI");
            for (int index = 0; index < whereList.Count; index++)
            {
                sb.AppendFormat("\t\t\t\t\t\t\t\t\t\t\t\t\t\t`{0}`=?{0}{1}\r\n", whereList[index].COLUMN_NAME, index == whereList.Count - 1 ? "" : " AND ");
            }
            sb.Append("\t\t\t\t\t\t\t\t\t\t\t\t\t\",new {");            for (int index = 0; index < keysList.Count; index++) { sb.AppendFormat("{0}={0}{1}", keysList[index].COLUMN_NAME, index == keysList.Count - 1 ? "" : ","); }
            sb.AppendFormat("}}).ToList<{0}Info>();\r\n", ClassName);
            sb.Append("return list;\r\n");
            sb.Append("}\r\n");
            sb.Append("}\r\n");
            sb.Append("#endregion\r\n\r\n");





            sb.AppendFormat("#region -- GetListAndCount {0} --\r\n", TableName);
            sb.AppendFormat("public static List<{0}Info> Get{0}Infos(int pageIndex,int pageSize", ClassName);
            for (int index = 0; index < keysList.Count; index++)
            {
                sb.AppendFormat(",{0} {1}", GetCsharpType(keysList[index].DATA_TYPE), keysList[index].COLUMN_NAME);
            }
            sb.Append(",ref int totalRec)\r\n");
            sb.Append("{\r\n");
            sb.Append("using (IDbConnection connection = new MySqlConnection(Config.ConnectionString))\r\n");
            sb.Append("{\r\n");
            sb.AppendFormat("totalRec= connection.ExecuteScalar<int>(@\"select count(*) from `{0}` WHERE\r\n", TableName);
            whereList = list.FindAll(a => a.COLUMN_KEY == "PRI");
            for (int index = 0; index < whereList.Count; index++)
            {
                sb.AppendFormat("\t\t\t\t\t\t\t\t\t\t\t\t\t\t`{0}`=?{0}{1}\r\n", whereList[index].COLUMN_NAME, index == whereList.Count - 1 ? "" : ",");
            }
            sb.Append("\t\t\t\t\t\t\t\t\t\t\t\t\t\",new {");
            for (int index = 0; index < keysList.Count; index++)
            {
                sb.AppendFormat("{0}={0}{1}\r\n", keysList[index].COLUMN_NAME, index == keysList.Count - 1 ? "" : ",");
            }
            sb.Append("});\r\n");
            sb.AppendFormat("var list= connection.Query<{0}Info>(@\"select \r\n", ClassName);
            for (int index = 0; index < list.Count; index++)
            {
                sb.AppendFormat("\t\t\t\t\t\t\t\t\t\t\t\t\t\t`{0}`{1}\r\n", list[index].COLUMN_NAME, index == list.Count - 1 ? "" : ",");
            }
            sb.AppendFormat("\t\t\t\t\t\t\t\t\t\t\t\t\tFROM `{0}`\r\n", TableName);
            sb.Append("\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE\r\n");
            whereList = list.FindAll(a => a.COLUMN_KEY == "PRI");
            for (int index = 0; index < whereList.Count; index++)
            {
                sb.AppendFormat("\t\t\t\t\t\t\t\t\t\t\t\t\t\t`{0}`=?{0}{1}\r\n", whereList[index].COLUMN_NAME, index == whereList.Count - 1 ? "" : " AND ");
            }
            sb.Append("\t\t\t\t\t\t\t\t\t\t\t\t\tlimit ?start,?size\r\n");
            sb.Append("\t\t\t\t\t\t\t\t\t\t\t\t\t\",new {start = (pageIndex - 1) * pageSize, size = pageSize");
            for (int index = 0; index < keysList.Count; index++)
            {
                sb.AppendFormat(",{0}={0}\r\n", keysList[index].COLUMN_NAME);
            }
            sb.AppendFormat("}}).ToList<{0}Info>();\r\n", ClassName);
            sb.Append("return list;\r\n");
            sb.Append("}\r\n");
            sb.Append("}\r\n");
            sb.Append("#endregion\r\n\r\n");




            sb.AppendFormat("#region -- GetObject {0} --\r\n", TableName);
            sb.Append("public static object Get("); 
            for (int index = 0; index < keysList.Count; index++)
            {
                sb.AppendFormat("{0} {1}{2}", GetCsharpType(keysList[index].DATA_TYPE), keysList[index].COLUMN_NAME, index == keysList.Count - 1 ? "" : ",");
            }
            sb.Append(")\r\n");
            sb.Append("{\r\n");
            sb.Append("using (IDbConnection connection = new MySqlConnection(Config.ConnectionString))\r\n");
            sb.Append("{\r\n");
            sb.AppendFormat("var result = connection.ExecuteScalar<object>(@\"select object from `{0}` WHERE\r\n", TableName);
            whereList = list.FindAll(a => a.COLUMN_KEY == "PRI");
            for (int index = 0; index < whereList.Count; index++)
            {
                sb.AppendFormat("\t\t\t\t\t\t\t\t\t\t\t\t\t\t`{0}`=?{0}{1}\r\n", whereList[index].COLUMN_NAME, index == whereList.Count - 1 ? "" : ",");
            }
            sb.Append("\t\t\t\t\t\t\t\t\t\t\t\t\t\",new {");
            for (int index = 0; index < keysList.Count; index++)
            {
                sb.AppendFormat("{0}={0}{1}\r\n", keysList[index].COLUMN_NAME, index == keysList.Count - 1 ? "" : ",");
            }
            sb.Append("});\r\n"); 

            sb.Append("return result > 0;\r\n");
            sb.Append("}\r\n");
            sb.Append("}\r\n");
            sb.Append("#endregion\r\n\r\n");




            return sb.ToString();
        }


        public static string GetCsharpType(string sqlType)
        {
            switch (sqlType)
            {
                case "int":
                    return "int";
                case "varchar":
                case "text":
                    return "string";
                case "datetime":
                    return "DateTime";
                case "decimal":
                    return "decimal";
                case "bit":
                    return "bool";
                default:
                    return sqlType+"==>unknow";
            }
        }

        public static List<dynamic> GetTableDetails()
        {

            using (IDbConnection connection = new MySqlConnection(ConnectionString))
            {
                var list = connection.Query<dynamic>("select * from `information_schema`.`COLUMNS`  where table_name='" + TableName + "'").ToList();
                return list;
            }
        }


        public static List<dynamic> GetTables(string dbname)
        {
            using (IDbConnection connection = new MySqlConnection(ConnectionString))
            {
                var list = connection.Query<dynamic>("select * from `information_schema`.`TABLES`  where table_schema='" + dbname + "'").ToList();
                return list;
            }
        }

        private static string GetEntitiesCode(List<dynamic> list)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("public class {0}Info\r\n", ClassName);
            sb.Append("{\r\n");
            foreach (var item in list)
            {
                sb.AppendFormat("\t\tpublic {0} {1} {{ get;set; }}\r\n", GetCsharpType(item.DATA_TYPE), item.COLUMN_NAME);
            }
            sb.Append("}\r\n");
            return sb.ToString();
        }


        private static string GetColumnStrings(List<dynamic> list, string prefix, string suffix, bool IsAll = false)
        {
            StringBuilder sb = new StringBuilder();
            foreach (var item in list)
            {
                if (!IsAll)
                {
                    if (item.EXTRA.ToString() == "auto_increment")
                        continue;
                }
                if (sb.Length > 0)
                    sb.Append(",");
                sb.AppendFormat("{0}{1}{2}", prefix, item.COLUMN_NAME,suffix);
            }

            return sb.ToString();
        }

    }
}

written by ocean

1月 22

装PHPStudy,Apache启动不了.提示80端口占用

这次不想用360处理了,找了一个命令,不得不写个post记录一下了

CMD中查找80端口

netstat -aon | findstr "80"

然后再记录中找到PID

去task Manager中结束掉.我KAO,这次居然是Skype

 

又一次出现了这个问题,结果发现pid=4,居然是system。

这下傻眼了,总不能把system结束了。

用另外一个指令

netsh http show servicestate

在最后看到了这个

Request queues:
    Request queue name: Request queue is unnamed.
        Version: 1.0
        State: Active
        Request queue 503 verbosity level: Basic
        Max requests: 1000
        Number of active processes attached: 1
        Process IDs:
            3464

    Request queue name: Request queue is unnamed.
        Version: 1.0
        State: Active
        Request queue 503 verbosity level: Basic
        Max requests: 1000
        Number of active processes attached: 1
        Process IDs:
            3464

    Request queue name: Request queue is unnamed.
        Version: 1.0
        State: Active
        Request queue 503 verbosity level: Basic
        Max requests: 1000
        Number of active processes attached: 1
        Process IDs:
            3464

PID=3464

原来是Report server,知道怎么做了

written by ocean

1月 07

经过几天的准备,我的站点古诗大全终于跑在Linode的Linux主机上了,哈哈

使用的是Mono+Jexus+Mysql+ASP.NET的结构

旧有程序作了小修改,主要是路径问题  "/" 在Linux中作为根目录处理,所以需要改成相对路径不能相对站点根

另外记录一些Linux的命令,这个确实以前用的不多

首先时间问题

因为服务器在国外,时区和国内对不上,需要调整成为北京时区

linux查看系统时间:

date -R

编辑时区文件

vi /etc/sysconfig/clock

编辑内容如下

ZONE="Asia/Shanghai"
UTC=false
ARC=false

删除默认配置

rm -rf /etc/localtime

重新指定配置

ln -s /usr/share/zoneinfo/Asia/Shanghai /etc/localtime

同步系统时间

ntpdate -u pool.ntp.org

C#连接mysql字符串需要指定CharSet,否则会出现乱码

"Server=localhost;Database=***;Uid=***;Pwd=***;CharSet=utf8;Allow Zero Datetime=True";

另外mysql外网连接需要授权

GRANT ALL PRIVILEGES ON *.* TO 'user'@'ip' IDENTIFIED BY 'password' WITH GRANT OPTION;

例如

GRANT ALL PRIVILEGES ON *.* TO 'root'@'203.23.45.34' IDENTIFIED BY '123456' WITH GRANT OPTION;

修改MySql的root密码

update user set password=passworD("123456") where user='root';

设置juxus开机自启动

sudo vi /etc/rc.local

最后添加这一条

/usr/jexus/jws start

设置juxus为系统服务

cd /etc/init.d/       
vi jws

编辑jws内容如下

#!/bin/bash
#chkconfig: 2345 80 05
#description:jws
#

. /etc/rc.d/init.d/functions

case "$1" in
start)
  echo "Jexus Start.."
  /usr/jexus/jws start
  ;;
stop)
  echo "Jexus Stop.."
  /usr/jexus/jws stop
  ;;
restart)
  echo "Jexus Restart"
  /usr/jexus/jws restart
  ;;
status)
  /usr/jexus/jws status
  ;;
*)
  exit 1
  ;;
esac

exit $RETVAL

Linux主机URL大小写问题

Linux下URL是大小写敏感的,

修改文件忽略大小写

vi /usr/jexus/jws

启用

export MONO_IOMAP="all"

即可

启动停止JEXUS站点

启动:sudo /usr/jexus/jws start
停止:sudo /usr/jexus/jws stop
重启:sudo /usr/jexus/jws restart
重启指定网站:sudo /usr/jexus/jws restart siteName
停止指定网站:sudo /usr/jexus/jws stop siteName

同时使Jexus支持PHP

首先获得PHP的环境

yum install -y php php-mysql php-common php-gd php-mbstring php-mcrypt php-devel php-xml

使用Jexus自带的cgi支持php

首先需要修改php.ini

因为是默认安装的,所以默认地址为 "/etc/php.ini"

找到“cgi.force_redirect=1”这一行,然后把前面的注释去掉,并把“1”改为“0”

保存退出

然后,我们找到Jexus程序的存放目录,并修改“Jws.conf”文件。

把“php-fcgi.set=/usr/bin/php-cgi,6”前面的注释去除,这样Jexus就就可以通过CGI的方式支持PHP了

保存退出

最后还有一点

/jexus/siteconf/php.com

这个文件

修改

fastcgi.add=php|socket:/var/run/jexus/phpsvr

最后给一个该文件的例子

######################
# Web Site: Default 
########################################

port=80
root=/ /var/www/ectouch
hosts=wx.wx6.org


# addr=0.0.0.0
# CheckQuery=false
NoLog=true
# NoFile=/index.aspx
# Keep_Alive=false
# UseGZIP=true
# UseHttps=true
# DenyFrom=192.168.0.233, 192.168.1.*, 192.168.2.0/24
# AllowFrom=192.168.*.*
# DenyDirs=~/cgi, ~/upfiles
indexes=index.php
rewrite=^/mobile	/mobile/
# reproxy=/bbs/ http://192.168.1.112/bbs/

# Jexus php fastcgi address is '/var/run/jexus/phpsvr'
#######################################################
fastcgi.add=php|socket:/var/run/jexus/phpsvr

# php-fpm listen address is '127.0.0.1:9000'
############################################
# fastcgi.add=php|tcp:127.0.0.1:9000

written by ocean