个人博客

wpf学习

zgc — 2023/12/23 15:19:31

Svg转换Image工具

zgc — 2021/6/13 21:17:48

svg转换成图片工具可批量更改svg格式为jpg，png，bmp。

在GridControl控件中使用PopupContainerEdit构建数据快速输入检索

zgc — 2021/4/14 15:47:37

程序员大本营技术文章内容聚合第一站

在GridControl控件中使用PopupContainerEdit构建数据快速输入检索

这几天一直被这个东西困扰，不过最终还是解决了，所以分享一下。

效果图如下：

这边我就说一个，就是药品名称的检索（看懂一个其它都大同小异）。

第一步，你需要创建一个用户控件，这个控件就是你下拉时来显示数据的，如下图：

我这里用户控件名为YpxxList

第二步，需要在GridView中你需要实现下拉的列，ColumnEdit类型选PopupContainerEdit（这是它的类型RepositoryItemPopupContainerEdit）

这里重点，改好类型后这里会显示出来，要记得改一下TextEditStyle的类型，它默认是不让你在下拉框中输入东西，这样你就不能检索数据，这个问题我搞了好久....

第三步，需要在主窗体里声明几个对象，如下：

private YpxxList ypxxList = null;   //药品下拉列表（就是我刚才的用户控件对象）public delegate void UiHandleGrid(string text);//声明委托，需要跟用户控件进行传递数据，很重要public static event UiHandleGrid uihandleGrid;

然后在主窗体里开始编写代码，如下：

//这个方法放到主窗体的Load里private void InitYpxx(){//这个是RepositoryItemPopupContainerEdit的子，这个控件应该是类似于Winform中的PanelPopupContainerControl pccYpxxList = new PopupContainerControl();//把PopupContainerControl给RepositoryItemPopupContainerEdit的弹出控件（PopupControl）PopupContainerEdit_Ypxx.PopupControl = pccYpxxList;//然后设置一下显示的宽高pccYpxxList.Width = 300;pccYpxxList.Height = 200;ypxxList = new YpxxList();ypxxList.Dock = DockStyle.Fill;//将用户控件添加到PopupContainerControl中pccYpxxList.Controls.Add(ypxxList);//这里是用户控件的一个委托，刚才说过了主窗体的委托，它们就是用来互相传递一些数据的，比如我在下拉框中选择一行数据，我需要把几列值赋给主窗体上对应的几列ypxxList.getGridDialog += new YpxxList.GetGridDialog(YpxxList_getYpxxList);}//这个就是用户控件传递过来的一行选中的DataRow对象，我需要把一些值赋给主窗体的某些列private void YpxxList_getYpxxList(DataRow dr){gridView1.SetFocusedRowCellValue("ypmc", dr["itemname"]);gridView1.SetFocusedRowCellValue("dosageUnit", dr["doseunit"]);//popupContainerEdit.ClosePopup();}

第四步，就是给PopupContainerEdit添加两个事件，如下：

private void PopupContainerEdit_Ypxx_KeyDown(object sender, KeyEventArgs e){if (e.KeyCode == Keys.Down){PopupContainerEdit_Ypxx.PopupControl.Controls[0].Focus();}}//这个方法就是输入值时，把输入的值传给用户控件，用户控件进行检索。private void PopupContainerEdit_Ypxx_EditValueChanging(object sender, DevExpress.XtraEditors.Controls.ChangingEventArgs e){popupContainerEdit = sender as PopupContainerEdit;string srm = popupContainerEdit.Text;if (srm.Trim() != string.Empty){uihandleGrid(srm);popupContainerEdit.ShowPopup();popupContainerEdit.Focus();}else{popupContainerEdit.ClosePopup();}}

到这里主窗体的工作已完成，下面开始用户控件的代码编写，如下：‘

第一步，也是声明几个对象下面要用到，代码如下：

//放显示的数据private DataTable dt;//委托，把选中的行数据传递给主窗体public delegate void GetGridDialog(DataRow dr);public event GetGridDialog getGridDialog;

第二步，我就一次性把代码放出来了，偷个懒哈哈哈，如下：

public YpxxList(){InitializeComponent();InitList();//主窗体的委托Qphzcyxxd.uihandleGrid += new Qphzcyxxd.UiHandleGrid(frmYpxxAdvMain_uihandleGrid);}/// <summary>/// 根据输入的text来检索数据/// </summary>/// <param name="text"></param>void frmYpxxAdvMain_uihandleGrid(string text){//第一句根据你的需求替换即可，下面的应该是通用的var drs = dt.Select(string.Format("itemname like '%{0}%' or inputcode like '%{0}%'", text));var table = dt.Clone();foreach (var dr in drs){var temp = table.NewRow();temp.ItemArray = dr.ItemArray;table.Rows.Add(temp);}gcYpxx.DataSource = table;gcYpxx.RefreshDataSource();}/// <summary>/// 初始数据/// </summary>public void InitList(){//访问数据库得到你想要的数据源DataTalbe，然后给控件gcYpxx.DataSource = dt;}//GridView的双击事件private void gridView_DoubleClick(object sender, EventArgs e){if (gv_Ypxx.FocusedRowHandle > -1){//得到选中行的数据传递给主窗体var dr = gv_Ypxx.GetFocusedDataRow();getGridDialog(dr);}}//鼠标按下的事件private void gridView_KeyDown(object sender, KeyEventArgs e){if (e.KeyCode == Keys.Enter && gv_Ypxx.FocusedRowHandle > -1){//得到选中行的数据传递给主窗体var dr = gv_Ypxx.GetFocusedDataRow();getGridDialog(dr);}}

这样就大功告成了！！！首次用有些地方还不太懂，鄙人愚钝，有错的地方请明示。

补充：最近发现代码有点问题，下面进行补充；

需要把三个属性值改为“False” ，我遇到的问题是在进行检索后选择某个值，会出现下面这种情况：

本文链接：https://blog.csdn.net/MonkeyBananas/article/details/89713012

原作者删帖不实内容删帖广告或垃圾文章投诉

openwebkitsource

zgc — 2019/9/19 10:48:10

openwebkitsource

log4net代码配置

zgc — 2019/9/1 22:53:31

webconfig 里的配置！
  <log4net>
    <logger name="myLogger">
      <level value="All" />
      <appender-ref ref="AdoNetAppender_SqlServer" />
    </logger>
    <appender name="AdoNetAppender_SqlServer" type="log4net.Appender.AdoNetAppender">
      <!-- BufferSize 涓虹紦鍐插尯澶у皬锛屽彧鏈夋棩蹇楄褰曡秴5 鏉℃墠浼氫竴鍧楀啓鍏ュ埌鏁版嵁搴?-->
      <!-- 鎴栧啓涓?param name="BufferSize" value="10" /> -->
      <bufferSize value="50" />
      <!-- 寮曠敤 -->
      <!--2.0杩欐槸瀵瑰簲sql2008 濡傛槸2000鎴?005鍙﹀閰嶇疆-->
      <connectionType value="System.Data.SqlClient.SqlConnection, System.Data, Version=2.0.0.0, Culture=neutral,PublicKeyToken=b77a5c561934e089" />
      <!-- 杩炴帴鏁版嵁搴撳瓧绗︿覆 -->
      <connectionString value="Data Source=.\SQLEXPRESS;Initial Catalog=CaseManage;User ID=sa;Password=1234" />
      <!-- 鎻掑叆鍒拌〃Log -->
      <commandText value="INSERT INTO X_Log ([TIMESTAMP],[EVENTCATEGORY],[COMPUTERNAME],[MAC_ADDRESS],[USERNAME],[SOURCE],[DESCRIPTION],[COLLECTDATE]) VALUES (@log_date, @EventCategory, @ComputerName,@Mac_Address,@UserName,@Source,@Description,@CollectDate) " />
      <!-- 鏃ュ織绫诲瀷锛岃繖閲屽潎涓? -->
      <!-- 鏃ュ織璁板綍鏃堕棿锛孯awTimeStampLayout 涓洪粯璁ょ殑鏃堕棿杈撳嚭鏍煎紡 -->
      <parameter>
        <parameterName value="@log_date" />
        <dbType value="DateTime" />
        <layout type="log4net.Layout.RawTimeStampLayout" />
        <!-- /杩欓噷鍛㈡槸鑾峰彇log4net 涓彁渚涚殑鏃ュ織鏃堕棿 -->
      </parameter>
      <!-- 鏃ュ織鍒嗙被鎻忚堪 -->
      <parameter>
        <parameterName value="@EventCategory" />
        <dbType value="String" />
        <size value="50" />
        <layout type="AppBox.MyLayout,AppBox">
          <conversionPattern value="%property{EventCategory}" />
        </layout>
      </parameter>

      Source
      <parameter><parameterName value="@Source" /><dbType value="String" /><size value="5000" /><layout type="AppBox.MyLayout,AppBox"><conversionPattern value="%property{Source}" /></layout></parameter><!-- 璁＄畻鏈篒P --><parameter><parameterName value="@ComputerName" /><dbType value="String" /><size value="50" /><layout type="AppBox.MyLayout,AppBox"><conversionPattern value="%property{ComputerName}" /></layout></parameter><!-- 璁＄畻鏈篗ac 淇℃伅 --><parameter><parameterName value="@Mac_Address" /><dbType value="String" /><size value="50 " /><layout type="AppBox.MyLayout, AppBox"><conversionPattern value="%property{Mac_Address}" /></layout></parameter><!-- 鐧婚檰绯荤粺鐢ㄦ埛鍚?--><parameter><parameterName value="@UserName" /><dbType value="String" /><size value="50" /><layout type="AppBox.MyLayout,AppBox"><conversionPattern value="%property{UserName}" /></layout></parameter><!-- 浜嬩欢鎻忚堪 --><parameter><parameterName value="@Description " /><dbType value="String" /><size value="4000" /><layout type="AppBox.MyLayout, AppBox"><conversionPattern value="%property{Description}" /></layout></parameter><parameter><parameterName value="@CollectDate" /><dbType value="DateTime" /><layout type="log4net.Layout.RawTimeStampLayout" /></parameter></appender>
  </log4net>

loghelper.cs文件

 public class LogHelper
    {
        public LogHelper()
        {
            SetConfig();
        }
        private static readonly log4net.ILog loginfo = log4net.LogManager.GetLogger("myLogger");

        private static bool IsLoadConfig = false;
        private static void SetConfig()
        {
            log4net.Config.DOMConfigurator.Configure();
        }

        /// <summary>
        /// 记录日志
        /// </summary>
        /// <param name="info">提示信息</param>
        public static void WriteLog(string info)
        {
            if (!IsLoadConfig)
            {
                SetConfig();
                IsLoadConfig = true;
            }
            if (loginfo.IsInfoEnabled)
            {
                loginfo.Info(info);
            }
        }

        /// <summary>
        /// 记录日志
        /// </summary>
        /// <param name="info">提示信息</param>
        public static void WriteLog(object info)
        {
            if (!IsLoadConfig)
            {
                SetConfig();
                IsLoadConfig = true;
            }
            if (loginfo.IsInfoEnabled)
            {
                loginfo.Info(info);
            }
        }

        /// <summary>
        /// 写日志
        /// </summary>
        /// <param name="LogType">日志类型</param>
        /// <param name="Message"></param>
        public  void WriteLog(string LogType,string Message,string IP,string UserName,string RequestUrl )
        {
            LogContent logmodel = new LogContent();
             PageBase Base=new PageBase();
             logmodel.EventCategory = LogType;
             logmodel.ComputerName = IP;
             logmodel.Source = RequestUrl;
            logmodel.Mac_Address = Base.GetMacAddress();
            logmodel.UserName = UserName;
            logmodel.Description = Message;
            LogHelper.WriteLog(logmodel);
    
        }

        /// <summary>
        /// 记录异常
        /// </summary>
        /// <param name="info">错误</param>
        /// <param name="ex">Exception</param>
        public static void WriteLog(string info, Exception ex)
        {
            if (!IsLoadConfig)
            {
                SetConfig();
                IsLoadConfig = true;
            }
            if (loginfo.IsErrorEnabled)
            {
                loginfo.Error(info, ex);
            }
        }


    }

logcontent.cs文件

 /// <summary>
    /// 包含了所有的自定字段属性
    /// </summary>
    public partial class LogContent
    {
        
        /// <summary>
        /// 日志分类描述，自定义
        /// </summary>
        public string EventCategory { get; set; }

        /// <summary>
        /// 日志分类描述，自定义
        /// </summary>
        public string Source { get; set; }
       
        /// <summary>
        /// 计算机IP
        /// </summary>
        public string ComputerName { get; set; }

        /// <summary>
        /// 计算机Mac 地址
        /// </summary>
        public string Mac_Address { get; set; }


        /// <summary>
        /// 系统登陆用户
        /// </summary>
        public string UserName { get; set; }
       
        
        /// <summary>
        /// 日志描述信息
        /// </summary>
        public string Description { get; set; }

    }

摘自某系统！

c#生成带logo的二维码

zgc — 2019/8/2 19:39:44

代码来源：https://github.com/codebude/QRCoder 的demo

    private void RenderQrCode()
        {
            string level = comboBoxECC.SelectedItem.ToString();
            QRCodeGenerator.ECCLevel eccLevel =
             (QRCodeGenerator.ECCLevel)(level == "L" ? 0 : level == "M" ? 1 : level == "Q" ? 2 : 3);
            using (QRCodeGenerator qrGenerator = new QRCodeGenerator())
            {
                using (QRCodeData qrCodeData = qrGenerator.CreateQrCode(textBoxQRCode.Text, eccLevel))
                {
                    using (QRCode qrCode = new QRCode(qrCodeData))
                    {

                        pictureBoxQRCode.BackgroundImage = qrCode.GetGraphic(20, Color.Black, Color.White,
                            GetIconBitmap(), (int) iconSize.Value);

                         this.pictureBoxQRCode.Size = new System.Drawing.Size(pictureBoxQRCode.Width,
                          pictureBoxQRCode.Height);
                        //Set the SizeMode to center the image.
                        this.pictureBoxQRCode.SizeMode = PictureBoxSizeMode.CenterImage;

                        pictureBoxQRCode.SizeMode = PictureBoxSizeMode.StretchImage;
                    }
                }
            }
        }

        private Bitmap GetIconBitmap()
        {
            Bitmap img = null;
            if (iconPath.Text.Length > 0)
            {
                try
                {
                    img = new Bitmap(iconPath.Text);
                }
                catch (Exception)
                {
                }
            }
            return img;
        }

附件附上图片及编译好的汉化过得qrcoder.dll

qrcoder.dll 目前支持.netframework 3.5以上

/// <summary>
        /// 生成彩色二维码        /// </summary>
        /// <param name="data">二维码内容</param>
        /// <param name="filename">生成的图片名称（如：123.jpg）</param>
        /// <param name="filepath">图片存放路径（如：Images\\CustomerQRCode）</param>
        /// <returns></returns>
        public static bool CreateQRCode(string data, string filename, string filepath)
        {            if (string.IsNullOrWhiteSpace(data) || string.IsNullOrWhiteSpace(filename) || string.IsNullOrWhiteSpace(filepath))                return false;

            QRCodeWriter writer = new QRCodeWriter();
            Hashtable hints = new Hashtable();
            hints.Add(EncodeHintType.CHARACTER_SET, "UTF-8");
            hints.Add(EncodeHintType.ERROR_CORRECTION, ErrorCorrectionLevel.H);
            hints.Add(EncodeHintType.VERSION_START, 5);

            Bitmap image = writer.encode(data, BarcodeFormat.QR_CODE, 0x200, 0x200, hints).ToBitmap();//黑白二维码
            Bitmap bitmap2 = new Bitmap(image.Width, image.Height, PixelFormat.Format32bppArgb);
            Graphics graphics = Graphics.FromImage(bitmap2);
            graphics.SmoothingMode = SmoothingMode.AntiAlias;
            graphics.DrawImage(image, 0, 0);
            image.Dispose();

            Bitmap bitmap3 = QrCodeVertical(bitmap2.Width, bitmap2.Height);//彩色渐变二维码

            Color color = Color.FromArgb(200, 224, 114, 1);            int num = 140;            try
            {
                num -= (Encoding.UTF8.GetBytes(data).Length - 20) / 2;
            }            catch (Exception)
            {
            }            int num2 = num;            int num3 = num2;            for (int i = 0; i < bitmap2.Width; i++)
            {                for (int j = 0; j < bitmap2.Height; j++)
                {
                    Color color3;
                    Color pixel = bitmap2.GetPixel(i, j);                    if ((i < num2) && (j < num3))
                    {
                        color3 = ((pixel.A == 0xff) && (pixel.B == 0)) ? color : pixel;
                    }                    else
                    {
                        color3 = ((pixel.A == 0xff) && (pixel.B == 0)) ? bitmap3.GetPixel(i, j) : pixel;
                    }
                    bitmap2.SetPixel(i, j, color3);
                }
            }
            bitmap3.Dispose();            #region 添加二维码标题            string str2 = "";//标题一
            if (str2.Length < 1)
            {
                str2 = "MyTest";
            }            float emSize = 32f;
            emSize -= (str2.Length - 4) * 1.8f;
            Font font = new Font("微软雅黑", emSize, FontStyle.Bold);
            SizeF ef = graphics.MeasureString(str2, font);            float num7 = (bitmap2.Width - ef.Width) / 2f;
            Brush brush = new SolidBrush(Color.FromArgb(0xff, 0x3a, 0xb2, 0xc2));
            Brush brush2 = new SolidBrush(Color.White);            int y = 50;
            graphics.FillRectangle(brush2, new Rectangle((int)num7, y, (int)ef.Width, (int)ef.Height));
            graphics.DrawString(str2, font, brush, (float)((int)num7), (float)y);
            Brush brush3 = new SolidBrush(Color.FromArgb(0xff, 0x3a, 0xb2, 0xc2));            int width = 140;
            graphics.FillEllipse(brush2, (bitmap2.Width - width) / 2, (bitmap2.Height - width) / 2, width, width);            int num10 = 0x80;
            graphics.FillEllipse(brush3, (bitmap2.Width - num10) / 2, (bitmap2.Height - num10) / 2, num10, num10);            int num11 = 110;
            graphics.FillEllipse(brush2, (bitmap2.Width - num11) / 2, (bitmap2.Height - num11) / 2, num11, num11);            #endregion

            #region 添加二维码中间内容 
            str2 = "";//标题二
            if (str2.Length < 1)
            {
                str2 = "Test";
            }            float num12 = 32f;
            num12 -= (str2.Length - 3) * 3.5f;
            Font font2 = new Font("Meiryo", num12, FontStyle.Bold);
            ef = graphics.MeasureString(str2, font2);            float x = ((bitmap2.Width - ef.Width) / 2f) + 2f;            float num14 = ((bitmap2.Height - ef.Height) / 2f) + 8f;
            graphics.DrawString(str2, font2, brush3, x, num14);
            graphics.Dispose();            #endregion


            filepath = System.Web.HttpContext.Current.Server.MapPath(@"~\"+ filepath) + "\\" + filename;            //filepath = filepath + filename;
            System.IO.FileStream fs = new System.IO.FileStream(filepath, System.IO.FileMode.OpenOrCreate, System.IO.FileAccess.Write);
            
            bitmap2.Save(fs, System.Drawing.Imaging.ImageFormat.Jpeg);

            fs.Close();
            image.Dispose();            return true;

        }

C# 汉语生成拼音(微软官方方案)

zgc — 2019/4/24 18:03:06

项目开发中用户的登录名是管理员在后台输入汉字姓名后自动生成的拼音。例如输入：张三，则登录名自动生成ZHANGSAN。

我在网上查阅了很多方法：

如：http://blog.csdn.net/nengzhong/article/details/4210039 等一些方法，但是在转换过程中，有些中文它是无法识别的。

于是，又东奔西走，终于在为微软的官方找到了最佳解决方案：

微软提供了一个Microsoft Visual Studio International Pack 类库软件包，vsintlpack1.zip

其中有一个类库:Simplified Chinese Pin-Yin Conversion Library 支持获取简体中文字符的常用属性

具体操作：

1、vsintlpack1.zip 下载地址为：

http://www.microsoft.com/downloads/details.aspx?FamilyID=44cac7f0-633b-477d-aed2-99aee642fc10&DisplayLang=zh-cn

2、下载vsintlpack1.zip后，解压安装其中的CHSPinYinConv.msi，如安装到C:\Program Files\Microsoft Visual Studio International Pack\Simplified Chinese Pin-Yin Conversion Library ，可以看到以下文件。

3、在项目中添加引用，在步骤2安装目录下添加引用ChnCharInfo.dll。

引用成功后如图：

4 、测试方法如下：

private string ConvertToPinYin(string str)
        {
            string PYstr = "";
            foreach (char item in str.ToCharArray())
            {
                if (Microsoft.International.Converters.PinYinConverter.ChineseChar.IsValidChar(item))
                {
                    Microsoft.International.Converters.PinYinConverter.ChineseChar cc = new Microsoft.International.Converters.PinYinConverter.ChineseChar(item);
                    //PYstr += string.Join("", cc.Pinyins.ToArray());
                    PYstr += cc.Pinyins[0].Substring(0, cc.Pinyins[0].Length - 1);
                    //PYstr += cc.Pinyins[0].Substring(0, cc.Pinyins[0].Length - 1).Substring(0, 1).ToLower();
                }
                else
                {
                    PYstr += item.ToString();
                }
            }
            return PYstr;
        }

当你的才华还撑不起你的野心时，那你就应该静下心来学习;当你的能力还驾驭不了你的目标时，那就应该沉下心来历练！

转自：https://www.cnblogs.com/guangang/articles/5577044.html

SQLiteDataStore

zgc — 2019/4/16 11:31:04

  public partial class SQLiteDataStore : SQLStoreBase<SqlEntityInfo>, IDisposable
    {
        private string m_connectionString;
        private string Password { get; set; }
        public string FileName { get; protected set; }
        
        protected SQLiteDataStore()
            : base()
        {
        }
        public SQLiteDataStore(string fileName)
            : this(fileName, null)
        {
        }
        public SQLiteDataStore(string fileName, string password)
            : this()
        {
            if (string.IsNullOrEmpty(fileName))
            {
                throw new ArgumentException();
            }
            FileName = fileName;
            Password = password;
        }
        public override string Name
        {
            get { return FileName; }
        }
        protected override string DefaultDateGenerator
        {
            get { return "CURRENT_TIMESTAMP"; }
        }
        public override string ConnectionString
        {
            get
            {
                if (m_connectionString == null)
                {
                    m_connectionString = string.Format("Data Source={0};", FileName);
                    if (!string.IsNullOrEmpty(Password))
                    {
                        m_connectionString += string.Format("Password={0};", Password);
                    }
                }
                return m_connectionString;
            }
        }
        protected override IDbCommand GetNewCommandObject()
        {
            return new SQLiteCommand();
        }
        protected override IDbConnection GetNewConnectionObject()
        {
            return new SQLiteConnection(ConnectionString);
        }
        protected override IDataParameter CreateParameterObject(string parameterName, object parameterValue)
        {
            return new SQLiteParameter(parameterName, parameterValue);
        }
        protected override string AutoIncrementFieldIdentifier
        {
            get { return "AUTOINCREMENT"; }
        }
        public override void CreateStore()
        {
            if (StoreExists)
            {
                throw new StoreAlreadyExistsException();
            }
#if(!WINDOWS_PHONE)
            SQLiteConnection.CreateFile(FileName);
#endif
            var connection = GetConnection(true);
            try
            {
                foreach (var entity in this.Entities)
                {
                    CreateTable(connection, entity);
                }
            }
            finally
            {
                DoneWithConnection(connection, true);
            }
        }
        public override void DeleteStore()
        {
            if (StoreExists)
            {
                File.Delete(FileName);
            }
        }
        public override bool StoreExists
        {
            get { return File.Exists(FileName); }
        }
        protected override string ParameterPrefix
        {
            get { return "@"; }
        }
        private SQLiteCommand GetInsertCommand(string entityName)
        {
            // TODO: support command caching to improve bulk insert speeds
            //       simply use a dictionary keyed by entityname
            var keyScheme = Entities[entityName].EntityAttribute.KeyScheme;
            var insertCommand = new SQLiteCommand();
            
            var sbFields = new StringBuilder(string.Format("INSERT INTO {0} (", entityName));
            var sbParams = new StringBuilder( " VALUES (");
            foreach (var field in Entities[entityName].Fields)
            {
                // skip auto-increments
                if ((field.IsPrimaryKey) && (keyScheme == KeyScheme.Identity))
                {
                    continue;
                }
                sbFields.Append(field.FieldName + ",");
                sbParams.Append(ParameterPrefix + field.FieldName + ",");
                insertCommand.Parameters.Add(new SQLiteParameter(ParameterPrefix + field.FieldName, field.DataType));
            }
            // replace trailing commas
            sbFields[sbFields.Length - 1] = ')';
            sbParams[sbParams.Length - 1] = ')';
            insertCommand.CommandText = sbFields.ToString() + sbParams.ToString();
            return insertCommand;
        }
        
        /// <summary>
        /// Inserts the provided entity instance into the underlying data store.
        /// </summary>
        /// <param name="item"></param>
        /// <remarks>
        /// If the entity has an identity field, calling Insert will populate that field with the identity vale vefore returning
        /// </remarks>
        public override void OnInsert(object item, bool insertReferences)
        {
            if (item is DynamicEntity)
            {
                OnInsertDynamicEntity(item as DynamicEntity, insertReferences);
                return;
            }
            var itemType = item.GetType();
            string entityName = m_entities.GetNameForType(itemType);
            var keyScheme = Entities[entityName].EntityAttribute.KeyScheme;
            if (entityName == null)
            {
                throw new EntityNotFoundException(item.GetType());
            }
            // ---------- Handle N:1 References -------------
            if (insertReferences)
            {
                DoInsertReferences(item, entityName, keyScheme, true);
            }
            var connection = GetConnection(false);
            try
            {
                FieldAttribute identity = null;
                var command = GetInsertCommand(entityName);
                command.Connection = connection as SQLiteConnection;
                command.Transaction = CurrentTransaction as SQLiteTransaction;
                // TODO: fill the parameters
                foreach (var field in Entities[entityName].Fields)
                {
                    if ((field.IsPrimaryKey) && (keyScheme == KeyScheme.Identity))
                    {
                        identity = field;
                        continue;
                    }
                    else if (field.DataType == DbType.Object)
                    {
                        // get serializer
                        var serializer = GetSerializer(itemType);
                        if (serializer == null)
                        {
                            throw new MissingMethodException(
                                string.Format("The field '{0}' requires a custom serializer/deserializer method pair in the '{1}' Entity",
                                field.FieldName, entityName));
                        }
                        var value = serializer.Invoke(item, new object[] { field.FieldName });
                        if (value == null)
                        {
                            command.Parameters[ParameterPrefix + field.FieldName].Value = DBNull.Value;
                        }
                        else
                        {
                            command.Parameters[ParameterPrefix + field.FieldName].Value = value;
                        }
                    }
                    else if (field.DataType == DbType.DateTime)
                    {
                        var dtValue = GetInstanceValue(field, item);
                        if (dtValue.Equals(DateTime.MinValue) &&
                            ((field.AllowsNulls) || (field.DefaultType == DefaultType.CurrentDateTime)))
                        {
                            // testing of just letting the null fall through is setting the field to null, not using the default
                            // so we'll set it manually
                            dtValue = DateTime.Now;
                        }
                        command.Parameters[ParameterPrefix + field.FieldName].Value = dtValue;
                    }
                    else if (field.IsRowVersion)
                    {
                        // read-only, so do nothing
                    }
                    else if (field.PropertyInfo.PropertyType.UnderlyingTypeIs<TimeSpan>())
                    {
                        // SQLite doesn't support "timespan" - and date/time must be stored as text, real or 64-bit integer (see the SQLite docs for more details)
                        // here we'll store TimeSpans (since they can be negative) as an offset from a base date
                        var value = field.PropertyInfo.GetValue(item, null);
                        if (value == null)
                        {
                            command.Parameters[ParameterPrefix + field.FieldName].Value = DBNull.Value;
                        }
                        else
                        {
                            var storeTime = new DateTime(1980, 1, 1) + (TimeSpan)value;
                            command.Parameters[ParameterPrefix + field.FieldName].Value = storeTime;
                        }
                    }
                    else
                    {
                        var value = field.PropertyInfo.GetValue(item, null);
                        if ((value == null) && (field.DefaultValue != null))
                        {
                            command.Parameters[ParameterPrefix + field.FieldName].Value = field.DefaultValue;
                        }
                        else
                        {
                            command.Parameters[ParameterPrefix + field.FieldName].Value = value;
                        }
                    }
                }
                command.ExecuteNonQuery();
                // did we have an identity field?  If so, we need to update that value in the item
                if (identity != null)
                {
                    var id = GetIdentity(connection);
                    identity.PropertyInfo.SetValue(item, id, null);
                }
                if (insertReferences)
                {
                    // ---------- Handle 1:N References -------------
                    DoInsertReferences(item, entityName, keyScheme, false);
                }
            }
            finally
            {
                DoneWithConnection(connection, false);
            }
        }
        public override void CompactDatabase()
        {
            var connection = GetConnection(true);
            try
            {
                string sql = "VACUUM";
                using (var command = GetNewCommandObject())
                {
                    command.CommandText = sql;
                    command.Connection = connection;
                    command.Transaction = CurrentTransaction;
                    command.ExecuteNonQuery();
                }
            }
            finally
            {
                DoneWithConnection(connection, true);
            }
        }
        private int GetIdentity(IDbConnection connection)
        {
            using (var command = new SQLiteCommand("SELECT last_insert_rowid()", connection as SQLiteConnection))
            {
                object id = command.ExecuteScalar();
                return Convert.ToInt32(id);
            }
        }
        protected override void GetPrimaryKeyInfo(string entityName, out string indexName, out string columnName)
        {
            var connection = GetConnection(true);
            try
            {
                indexName = string.Empty;
                columnName = string.Empty;
                string sql = string.Format("PRAGMA table_info({0})", entityName);
                using (var command = GetNewCommandObject())
                {
                    command.CommandText = sql;
                    command.Connection = connection;
                    command.Transaction = CurrentTransaction;
                    using (var reader = command.ExecuteReader() as SQLiteDataReader)
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                // pk column is #5
                                if (Convert.ToInt32(reader[5]) != 0)
                                {
                                    columnName = reader[1] as string;
                                }
                            }
                        }
                    }
                }
            }
            finally
            {
                DoneWithConnection(connection, true);
            }
        }
        private void UpdateIndexCacheForType(string entityName)
        {
            if (!TableExists(entityName)) return;
            if (Entities.Contains(entityName))
            {
                // have we already cached this?
                if (((SqlEntityInfo)Entities[entityName]).IndexNames != null) return;
            }
            else
            {
                return;
            }
            // get all iindex names for the type
            var connection = GetConnection(true);
            try
            {
                string sql = string.Format("SELECT name FROM sqlite_master WHERE (tbl_name = '{0}')", entityName);
                using (var command = GetNewCommandObject())
                {
                    command.Connection = connection;
                    command.CommandText = sql;
                    command.Transaction = CurrentTransaction;
                    using (var reader = command.ExecuteReader())
                    {
                        List<string> nameList = new List<string>();
                        while (reader.Read())
                        {
                            nameList.Add(reader.GetString(0));
                        }
                        ((SqlEntityInfo)Entities[entityName]).IndexNames = nameList;
                    }
                }
            }
            finally
            {
                DoneWithConnection(connection, true);
            }
        }
        public override string[] GetTableNames()
        {
            var names = new List<string>();
            var connection = GetConnection(true);
            try
            {
                using (var command = GetNewCommandObject())
                {
                    if(this.CurrentTransaction != null)
                    {
                        command.Transaction = this.CurrentTransaction;
                    }
                    command.Connection = connection;
                    var sql = "SELECT name FROM sqlite_master WHERE type = 'table'";
                    command.CommandText = sql;
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            var n = reader.GetString(0);
                            if (n == "sqlite_sequence") continue;
                            names.Add(n);
                        }
                    }
                    return names.ToArray();
                }
            }
            finally
            {
                DoneWithConnection(connection, true);
            }
        }
        public override bool TableExists(string tableName)
        {
            var connection = GetConnection(true);
            try
            {
                using (var command = GetNewCommandObject())
                {
                    command.Connection = connection;
                    var sql = string.Format("SELECT COUNT(*) FROM sqlite_master WHERE type = 'table' AND name = '{0}'", tableName);
                    command.CommandText = sql;
                    command.Transaction = CurrentTransaction;
                    var count = Convert.ToInt32(command.ExecuteScalar());
                    return (count > 0);
                }
            }
            finally
            {
                DoneWithConnection(connection, true);
            }
        }
        protected override void ValidateTable(IDbConnection connection, IEntityInfo entity)
        {
            // first make sure the table exists
            if (!TableExists(entity.EntityAttribute.NameInStore))
            {
                CreateTable(connection, entity);
                return;
            }
            var fieldData = new List< object[]>();
            using (var command = new SQLiteCommand())
            {
                command.Connection = connection as SQLiteConnection;
                command.CommandText = string.Format("PRAGMA table_info({0})", entity.EntityName);
                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var values = new object[6];
                        reader.GetValues(values);
                        fieldData.Add(values);
                    }
                }
            }
            foreach(var field in entity.Fields)
            {
                // 0 = cid (column id)
                // 1 = name
                // 2 = type
                // 3 = notnull
                // 4 = dflt_value
                // 5 = pk
                var existing = fieldData.FirstOrDefault(f => string.Compare(f[1].ToString(), field.FieldName, true) == 0);
                if (existing == null)
                {
                    // field doesn't exist - we must create it
                    var alter = new StringBuilder(string.Format("ALTER TABLE {0} ", entity.EntityAttribute.NameInStore));
                    alter.Append(string.Format("ADD [{0}] {1} {2}",
                        field.FieldName,
                        GetFieldDataTypeString(entity.EntityName, field),
                        GetFieldCreationAttributes(entity.EntityAttribute, field)));
                    using (var command = new SQLiteCommand(alter.ToString(), connection as SQLiteConnection))
                    {
                        command.ExecuteNonQuery();
                    }
                }
                else
                {
                    // TODO: verify field length, etc.
                }
            }
            return;
        }
        protected override string VerifyIndex(string entityName, string fieldName, FieldSearchOrder searchOrder, IDbConnection connection)
        {
            bool localConnection = false;
            if (connection == null)
            {
                localConnection = true;
                connection = GetConnection(true);
            }
            try
            {
                var indexName = string.Format("ORM_IDX_{0}_{1}_{2}", entityName, fieldName,
                    searchOrder == FieldSearchOrder.Descending ? "DESC" : "ASC");
                if (m_indexNameCache.FirstOrDefault(ii => ii.Name == indexName) != null) return indexName;
                using (var command = GetNewCommandObject())
                {
                    command.Connection = connection;
                    var sql = string.Format("SELECT COUNT(*) FROM sqlite_master WHERE type = 'index' AND name = '{0}'", indexName);
                    command.CommandText = sql;
                    var i = (long)command.ExecuteScalar();
                    if (i == 0)
                    {
                        sql = string.Format("CREATE INDEX {0} ON {1}({2} {3})",
                            indexName,
                            entityName,
                            fieldName,
                            searchOrder == FieldSearchOrder.Descending ? "DESC" : string.Empty);
                        Debug.WriteLine(sql);
                        command.CommandText = sql;
                        command.Transaction = CurrentTransaction;
                        command.ExecuteNonQuery();
                    }
                    var indexinfo = new IndexInfo
                    {
                        Name = indexName,
                        MaxCharLength = -1
                    };
                    m_indexNameCache.Add(indexinfo);
                }
                return indexName;
            }
            finally
            {
                if (localConnection)
                {
                    DoneWithConnection(connection, true);
                }
            }
        }
        protected override IEnumerable<object> Select(Type objectType, IEnumerable<FilterCondition> filters, int fetchCount, int firstRowOffset, bool fillReferences)
        {
            string entityName = m_entities.GetNameForType(objectType);
            if (entityName == null)
            {
                throw new EntityNotFoundException(objectType);
            }
            return Select(entityName, objectType, filters, fetchCount, firstRowOffset, fillReferences);
        }
        private IEnumerable<object> Select(string entityName, Type objectType, IEnumerable<FilterCondition> filters, int fetchCount, int firstRowOffset, bool fillReferences)
        {
            if (entityName == null)
            {
                throw new EntityNotFoundException(objectType);
            }
            if (!Entities.Contains(entityName))
            {
                if(DiscoverDynamicEntity(entityName) == null) yield return null;
            }
            UpdateIndexCacheForType(entityName);
            var items = new List<object>();
            var connection = GetConnection(false);
            SQLiteCommand command = null;
            try
            {
                CheckOrdinals(entityName);
                bool tableDirect;
                command = GetSelectCommand<SQLiteCommand, SQLiteParameter>(entityName, filters, out tableDirect);
                command.Connection = connection as SQLiteConnection;
                command.Transaction = CurrentTransaction as SQLiteTransaction;
                int searchOrdinal = -1;
            //    ResultSetOptions options = ResultSetOptions.Scrollable;
                object matchValue = null;
                string matchField = null;
            // TODO: we need to ensure that the search value does not exceed the length of the indexed
            // field, else we'll get an exception on the Seek call below (see the SQL CE implementation)
                using (var results = command.ExecuteReader(CommandBehavior.SingleResult))
                {
                    if (results.HasRows)
                    {
                        ReferenceAttribute[] referenceFields = null;
                        int currentOffset = 0;
                        if (matchValue != null)
                        {
                            // convert enums to an int, else the .Equals later check will fail
                            // this feels a bit kludgey, but for now it's all I can think of
                            if (matchValue.GetType().IsEnum)
                            {
                                matchValue = (int)matchValue;
                            }
                            if (searchOrdinal < 0)
                            {
                                searchOrdinal = results.GetOrdinal(matchField);
                            }
                        }
                        while (results.Read())
                        {
                            if (currentOffset < firstRowOffset)
                            {
                                currentOffset++;
                                continue;
                            }
                            // autofill references if desired
                            if (referenceFields == null)
                            {
                                referenceFields = Entities[entityName].References.ToArray();
                            }
                            bool fieldsSet;
                            object item = CreateEntityInstance(entityName, objectType, Entities[entityName].Fields, results, out fieldsSet);
                            object rowPK = null;
                            if(!fieldsSet)
                            {
                                foreach (var field in Entities[entityName].Fields)
                                {
                                    MethodInfo mi = null;
                                    if (!field.PropertyInfo.CanWrite)
                                    {
                                        // get a private accessor?
                                        mi = field.PropertyInfo.GetSetMethod(true);
                                        // not settable, so skip
                                        if (mi == null) continue;
                                    }
                                    var value = results[field.Ordinal];
                                    if (value != DBNull.Value)
                                    {
                                        if (field.DataType == DbType.Object)
                                        {
                                            if (fillReferences)
                                            {
                                                // get serializer
                                                var itemType = item.GetType();
                                                var deserializer = GetDeserializer(itemType);
                                                if (deserializer == null)
                                                {
                                                    throw new MissingMethodException(
                                                        string.Format("The field '{0}' requires a custom serializer/deserializer method pair in the '{1}' Entity",
                                                        field.FieldName, entityName));
                                                }
                                                var @object = deserializer.Invoke(item, new object[] { field.FieldName, value });
                                                field.PropertyInfo.SetValue(item, @object, null);
                                            }
                                        }
                                        else if (field.IsRowVersion)
                                        {
                                            // sql stores this an 8-byte array
                                            field.PropertyInfo.SetValue(item, BitConverter.ToInt64((byte[])value, 0), null);
                                        }
                                        else if (field.IsTimespan)
                                        {
                                            // SQLite doesn't support "timespan" - and date/time must be stored as text, real or 64-bit integer (see the SQLite docs for more details)
                                            // here we'll pull TimeSpans (since they can be negative) as an offset from a base date
                                            var storeDate = (DateTime)value;
                                            var storeTime = storeDate - new DateTime(1980, 1, 1);
                                            field.PropertyInfo.SetValue(item, storeTime, null);
                                        }
                                        else if (field.DataType == DbType.DateTime)
                                        {
                                            field.PropertyInfo.SetValue(item, Convert.ToDateTime(value), null);
                                        }
                                        else if ((field.IsPrimaryKey) && (value is Int64))
                                        {
                                            if (field.PropertyInfo.PropertyType.Equals(typeof(int)))
                                            {
                                                // SQLite automatically makes auto-increment fields 64-bit, so this works around that behavior
                                                field.PropertyInfo.SetValue(item, Convert.ToInt32(value), null);
                                            }
                                            else
                                            {
                                                field.PropertyInfo.SetValue(item, Convert.ToInt64(value), null);
                                            }
                                        }
                                        else if ((value is Int64) || (value is double))
                                        {
                                            // SQLite is "interesting" in that its 'integer' has a strong affinity toward 64-bit, so int and uint properties
                                            // end up as 64-bit fields.  Decimals have a strong affinity toward 'double', so float properties
                                            // end up as 'double'. Even more fun is that a decimal value '0' will come back as an int64
                                            // When we query those back, we must convert to put them into the property or we crash hard
                                            object setval;
                                            if (field.PropertyInfo.PropertyType.Equals(typeof(UInt32)))
                                            {
                                                setval = Convert.ToUInt32(value);
                                            }
                                            else if ((field.PropertyInfo.PropertyType.Equals(typeof(Int32))) || (field.PropertyInfo.PropertyType.Equals(typeof(Int32?))))
                                            {
                                                setval = Convert.ToInt32(value);
                                            }
                                            else if (field.PropertyInfo.PropertyType.Equals(typeof(decimal)))
                                            {
                                                setval = Convert.ToDecimal(value);
                                            }
                                            else if (field.PropertyInfo.PropertyType.Equals(typeof(float)))
                                            {
                                                setval = Convert.ToSingle(value);
                                            }
                                            else if (field.PropertyInfo.PropertyType.IsEnum)
                                            {
                                                setval = Enum.ToObject(field.PropertyInfo.PropertyType, value);
                                            }
                                            else
                                            {
                                                setval = value;
                                            }
                                            if (mi == null)
                                            {
                                                field.PropertyInfo.SetValue(item, setval, null);
                                            }
                                            else
                                            {
                                                mi.Invoke(setval, null);
                                            }
                                        }
                                        else
                                        {
                                            var t = value.GetType();
                                            field.PropertyInfo.SetValue(item, value, null);
                                        }
                                    }
                                    if (field.IsPrimaryKey)
                                    {
                                        rowPK = value;
                                    }
                                }
                            }
                            if ((fillReferences) && (referenceFields.Length > 0))
                            {
                                //FillReferences(item, rowPK, referenceFields, true);
                                FillReferences(item, rowPK, referenceFields, false);
                            }
                            // changed from
                            // items.Add(item);
                            yield return item;
                            if ((fetchCount > 0) && (items.Count >= fetchCount))
                            {
                                break;
                            }
                        }
                    }
                }
            }
            finally
            {
                if ((!UseCommandCache) && (command != null))
                {
                    command.Dispose();
                }
                if (UseCommandCache)
                {
                    Monitor.Exit(CommandCache);
                }
                FlushReferenceTableCache();
                DoneWithConnection(connection, false);
            }
        }
        
        public override void OnUpdate(object item, bool cascadeUpdates, string fieldName)
        {
            if (item is DynamicEntity)
            {
                OnUpdateDynamicEntity(item as DynamicEntity);
                return;
            }
            object keyValue;
            var changeDetected = false;
            var itemType = item.GetType();
            string entityName = m_entities.GetNameForType(itemType);
            var insertCommand = GetNewCommandObject();
            var updateSQL = new StringBuilder(string.Format("UPDATE {0} SET ", entityName));
            if (entityName == null)
            {
                throw new EntityNotFoundException(itemType);
            }
            if (Entities[entityName].Fields.KeyField == null)
            {
                throw new PrimaryKeyRequiredException("A primary key is required on an Entity in order to perform Updates");
            }
            var connection = GetConnection(false);
            try
            {
                CheckOrdinals(entityName);
                CheckPrimaryKeyIndex(entityName);
                using (var command = GetNewCommandObject())
                {
                    keyValue = Entities[entityName].Fields.KeyField.PropertyInfo.GetValue(item, null);
                    command.Connection = connection;
                    command.CommandText = string.Format("SELECT * FROM {0} WHERE [{1}] = {2}keyparam",
                        entityName,
                        Entities[entityName].Fields.KeyField.FieldName,
                        ParameterPrefix);
                    command.CommandType = CommandType.Text;
                    command.Parameters.Add(new SQLiteParameter(ParameterPrefix + "keyparam", keyValue));
                    command.Transaction = CurrentTransaction;
                    using (var reader = command.ExecuteReader() as SQLiteDataReader)
                    {
                        if (!reader.HasRows)
                        {
                            // TODO: the PK value has changed - we need to store the original value in the entity or diallow this kind of change
                            throw new RecordNotFoundException("Cannot locate a record with the provided primary key.  You cannot update a primary key value through the Update method");
                        }
                        reader.Read();
                        // update the values
                        foreach (var field in Entities[entityName].Fields)
                        {
                            // do not update PK fields
                            if (field.IsPrimaryKey)
                            {
                                continue;
                            }
                            else if (fieldName != null && field.FieldName != fieldName)
                            {
                                continue; // if we pass in a field name, skip over any fields that don't match
                            }
                            else if (field.IsRowVersion)
                            {
                                // read-only, so do nothing
                            }
                            else if (field.DataType == DbType.Object)
                            {
                                changeDetected = true;
                                // get serializer
                                var serializer = GetSerializer(itemType);
                                if (serializer == null)
                                {
                                    throw new MissingMethodException(
                                        string.Format("The field '{0}' requires a custom serializer/deserializer method pair in the '{1}' Entity",
                                        field.FieldName, entityName));
                                }
                                var value = serializer.Invoke(item, new object[] { field.FieldName });
                                if (value == null)
                                {
                                    updateSQL.AppendFormat("{0}=NULL, ", field.FieldName);
                                }
                                else
                                {
                                    updateSQL.AppendFormat("{0}={1}{0}, ", field.FieldName, ParameterPrefix);
                                    insertCommand.Parameters.Add(new SQLiteParameter(ParameterPrefix + field.FieldName, value));
                                }
                            }
                            else if (field.PropertyInfo.PropertyType.UnderlyingTypeIs<TimeSpan>())
                            {
                                changeDetected = true;
                                // SQL Compact doesn't support Time, so we're convert to ticks in both directions
                                var value = field.PropertyInfo.GetValue(item, null);
                                if (value == null)
                                {
                                    updateSQL.AppendFormat("{0}=NULL, ", field.FieldName);
                                }
                                else
                                {
                                    var ticks = ((TimeSpan)value).Ticks;
                                    updateSQL.AppendFormat("{0}={1}{0}, ", field.FieldName, ParameterPrefix);
                                    insertCommand.Parameters.Add(new SQLiteParameter(ParameterPrefix + field.FieldName, ticks));
                                }
                            }
                            else
                            {
                                var value = field.PropertyInfo.GetValue(item, null);
                                if (reader[field.FieldName] != value)
                                {
                                    changeDetected = true;
                                    if (value == null)
                                    {
                                        updateSQL.AppendFormat("{0}=NULL, ", field.FieldName);
                                    }
                                    else
                                    {
                                        updateSQL.AppendFormat("{0}={1}{0}, ", field.FieldName, ParameterPrefix);
                                        insertCommand.Parameters.Add(new SQLiteParameter(ParameterPrefix + field.FieldName, value));
                                    }
                                }
                            }
                        } // foreach
                    } // execute reader
                } // using command
            } // try
            finally
            {
                DoneWithConnection(connection, false);
            }
            // only execute if a change occurred
            if (changeDetected)
            {
                connection = GetConnection(false);
                try
                {
                    // remove the trailing comma and append the filter
                    updateSQL.Length -= 2;
                    updateSQL.AppendFormat(" WHERE {0} = {1}keyparam", Entities[entityName].Fields.KeyField.FieldName, ParameterPrefix);
                    insertCommand.Parameters.Add(new SQLiteParameter(ParameterPrefix + "keyparam", keyValue));
                    insertCommand.CommandText = updateSQL.ToString();
                    insertCommand.Connection = connection;
                    insertCommand.Transaction = CurrentTransaction;
                    insertCommand.ExecuteNonQuery();
                }
                finally
                {
                    DoneWithConnection(connection, false);
                }
            }
            if (cascadeUpdates)
            {
                // TODO: move this into the base DataStore class as it's not SqlCe-specific
                foreach (var reference in Entities[entityName].References)
                {
                    var itemList = reference.PropertyInfo.GetValue(item, null) as Array;
                    if (itemList != null)
                    {
                        foreach (var refItem in itemList)
                        {
                            if (!this.Contains(refItem))
                            {
                                var foreignKey = refItem.GetType().GetProperty(reference.ForeignReferenceField, BindingFlags.Instance | BindingFlags.Public);
                                foreignKey.SetValue(refItem, keyValue, null);
                                Insert(refItem, false);
                            }
                            else
                            {
                                Update(refItem, true, fieldName);
                            }
                        }
                    }
                }
            }
        }
        public override int Count<T>(IEnumerable<FilterCondition> filters)
        {
            var t = typeof(T);
            string entityName = m_entities.GetNameForType(t);
            if (entityName == null)
            {
                throw new EntityNotFoundException(t);
            }
            var connection = GetConnection(true);
            try
            {
                using (var command = BuildFilterCommand<SQLiteCommand, SQLiteParameter>(entityName, filters, true))
                {
                    command.Connection = connection as SQLiteConnection;
                    return (int)command.ExecuteScalar();
                }
            }
            finally
            {
                DoneWithConnection(connection, true);
            }
        }
        public override IEnumerable<T> Fetch<T>(int fetchCount, int firstRowOffset, string sortField, FieldSearchOrder sortOrder, FilterCondition filter, bool fillReferences)
        {
            throw new NotSupportedException("Fetch is not currently supported with this Provider.");
        }
        protected override string GetFieldDataTypeString(string entityName, FieldAttribute field)
        {
            // a SQLite Int64 auto-increment key requires being called "INTEGER", not "BIGINT"
            if(field.IsPrimaryKey && (field.DataType == DbType.Int64))
            {
                if (GetEntityInfo(entityName).EntityAttribute.KeyScheme == KeyScheme.Identity)
                {
                    return "INTEGER";
                }
            }
            return base.GetFieldDataTypeString(entityName, field);
        }
    }

SqlCeDataStore

zgc — 2019/4/16 11:23:52

   public partial class SqlCeDataStore : SQLStoreBase<SqlEntityInfo>
    {
        private string m_connectionString;
        private int m_maxSize = 128; // Max Database Size defaults to 128MB
        private string Password { get; set; }
        public string FileName { get; protected set; }
        protected SqlCeDataStore()
            : base()
        {
            UseCommandCache = true;
        }
        public SqlCeDataStore(string fileName)
            : this(fileName, null)
        {
        }
        public SqlCeDataStore(string fileName, string password)
            : this()
        {
            FileName = fileName;
            Password = password;
        }
        public override bool StoreExists
        {
            get
            {
                return File.Exists(FileName);
            }
        }
        public override string Name
        {
            get { return FileName; }
        }
        protected override IDbCommand GetNewCommandObject()
        {
            return new SqlCeCommand();
        }
        protected override string AutoIncrementFieldIdentifier
        {
            get { return "IDENTITY"; }
        }
        /// <summary>
        /// Deletes the underlying DataStore
        /// </summary>
        public override void DeleteStore()
        {
            File.Delete(FileName);
        }
        /// <summary>
        /// Creates the underlying DataStore
        /// </summary>
        public override void CreateStore()
        {
            if (StoreExists)
            {
                throw new StoreAlreadyExistsException();
            }
            // create the file
            using (SqlCeEngine engine = new SqlCeEngine(ConnectionString))
            {
                engine.CreateDatabase();
            }
            var connection = GetConnection(true);
            try
            {
                foreach (var entity in this.Entities)
                {
                    CreateTable(connection, entity);
                }
            }
            finally
            {
                DoneWithConnection(connection, true);
            }
        }
        /// <summary>
        /// Ensures that the underlying database tables contain all of the Fields to represent the known entities.
        /// This is useful if you need to add a Field to an existing store.  Just add the Field to the Entity, then 
        /// call EnsureCompatibility to have the field added to the database.
        /// </summary>
        public override void EnsureCompatibility()
        {
            if (!StoreExists)
            {
                CreateStore();
                return;
            }
            var connection = GetConnection(true);
            try
            {
                foreach (var entity in this.Entities)
                {
                    ValidateTable(connection, entity);
                }
            }
            finally
            {
                DoneWithConnection(connection, true);
            }
        }
        public override void CompactDatabase()
        {
            using (SqlCeEngine engine = new SqlCeEngine())
            {
                engine.Compact(ConnectionString);
            }
        }
        public override int Count<T>(IEnumerable<FilterCondition> filters)
        {
            var t = typeof(T);
            string entityName = m_entities.GetNameForType(t);
            if (entityName == null)
            {
                throw new EntityNotFoundException(t);
            }
            var connection = GetConnection(true);
            try
            {
                using (var command = BuildFilterCommand<SqlCeCommand, SqlCeParameter>(entityName, filters, true))
                {
                    command.Transaction = CurrentTransaction as SqlCeTransaction;
                    command.Connection = connection as SqlCeConnection;
                    return (int)command.ExecuteScalar();
                }
            }
            finally
            {
                DoneWithConnection(connection, true);
            }
        }
        /// <summary>
        /// Inserts the provided entity instance into the underlying data store.
        /// </summary>
        /// <param name="item"></param>
        /// <remarks>
        /// If the entity has an identity field, calling Insert will populate that field with the identity vale vefore returning
        /// </remarks>
        public override void OnInsert(object item, bool insertReferences)
        {
            string entityName;
            var itemType = item.GetType();
            if (item is DynamicEntity)
            {
                entityName = (item as DynamicEntity).EntityName;
            }
            else
            {
                entityName = m_entities.GetNameForType(itemType);
            }
            var keyScheme = Entities[entityName].EntityAttribute.KeyScheme;
            if (entityName == null)
            {
                throw new EntityNotFoundException(item.GetType());
            }
            if (insertReferences)
            {
                DoInsertReferences(item, entityName, keyScheme, true);
            }
            // we'll use table direct for inserts - no point in getting the query parser involved in this
            var connection = GetConnection(false);
            try
            {
                CheckOrdinals(entityName);
                FieldAttribute identity = null;
                using (var command = new SqlCeCommand())
                {
                    command.Connection = connection as SqlCeConnection;
                    command.Transaction = CurrentTransaction as SqlCeTransaction;
                    command.CommandText = entityName;
                    command.CommandType = CommandType.TableDirect;
                    using (var results = command.ExecuteResultSet(ResultSetOptions.Updatable))
                    {
                        var record = results.CreateRecord();
                        FillEntity(record.SetValue, entityName, itemType, item, out identity);
                        results.Insert(record);
                        // did we have an identity field?  If so, we need to update that value in the item
                        if (identity != null)
                        {
                            var id = GetIdentity(connection);
                            SetInstanceValue(identity, item, id);
                        }
                        if (insertReferences)
                        {
                            DoInsertReferences(item, entityName, keyScheme, false);
                        }
                    }
                    command.Dispose();
                }
            }
            finally
            {
                DoneWithConnection(connection, false);
            }
        }
        private void FillEntity(Action<int, object> setter, string entityName, Type itemType, object item, out FieldAttribute identity)
        {
            // The reason for this somewhat convoluted Action parameter is that while the SqlCeUpdateableRecord (from Insert) 
            // and SqlCeResultSet (from Update) both contain a SetValue method, they don't share it on any common
            // interface.  using an Action allows us to share this code anyway.
            identity = null;
            var keyScheme = Entities[entityName].EntityAttribute.KeyScheme;
            foreach (var field in Entities[entityName].Fields)
            {
                if (field.IsPrimaryKey)
                {
                    switch(keyScheme)
                    {
                        case KeyScheme.Identity:
                            identity = field;
                            break;
                        case KeyScheme.GUID:
                            var value = GetInstanceValue(field, item);
                            if (value.Equals(Guid.Empty))
                            {
                                value = Guid.NewGuid();
                                SetInstanceValue(field, item, value);
                            }
                            setter(field.Ordinal, value);
                            break;
                    }
                }
                else if (field.DataType == DbType.Object)
                {
                    // get serializer
                    var serializer = GetSerializer(itemType);
                    if (serializer == null)
                    {
                        throw new MissingMethodException(
                            string.Format("The field '{0}' requires a custom serializer/deserializer method pair in the '{1}' Entity",
                            field.FieldName, entityName));
                    }
                    var value = serializer.Invoke(item, new object[] { field.FieldName });
                    if (value == null)
                    {
                        setter(field.Ordinal, DBNull.Value);
                    }
                    else
                    {
                        setter(field.Ordinal, value);
                    }
                }
                else if (field.DataType == DbType.DateTime)
                {
                    var dtValue = GetInstanceValue(field, item);
                    if (dtValue.Equals(DateTime.MinValue))
                    {
                        if ((!field.AllowsNulls) && (field.DefaultType != DefaultType.CurrentDateTime))
                        {
                            dtValue = SqlDateTime.MinValue;
                            setter(field.Ordinal, dtValue);
                        }
                        else
                        {
                            // let the null pass through
                        }
                    }
                    else
                    {
                        setter(field.Ordinal, dtValue);
                    }
                }
                else if (field.IsRowVersion)
                {
                    // read-only, so do nothing
                }
                else
                {
                    var iv = GetInstanceValue(field, item);
                    
                    if((iv == DBNull.Value) && (field.DefaultValue != null))
                    {
                        iv = field.DefaultValue;
                    }
                    setter(field.Ordinal, iv);
                }
            }
        }
        protected override IDataParameter CreateParameterObject(string parameterName, object parameterValue)
        {
            return new SqlCeParameter(parameterName, parameterValue);
        }
        private int GetIdentity(IDbConnection connection)
        {
            using (var command = new SqlCeCommand("SELECT @@IDENTITY", connection as SqlCeConnection))
            {
                command.Transaction = CurrentTransaction as SqlCeTransaction;
                object id = command.ExecuteScalar();
                return Convert.ToInt32(id);
            }
        }
        protected override void GetPrimaryKeyInfo(string entityName, out string indexName, out string columnName)
        {
            indexName = string.Empty;
            columnName = string.Empty;
            var connection = GetConnection(true);
            try
            {
                string sql = string.Format("SELECT INDEX_NAME FROM information_schema.indexes WHERE (TABLE_NAME = '{0}') AND (PRIMARY_KEY = 1)", entityName);
                using (var command = GetNewCommandObject())
                {
                    command.Transaction = CurrentTransaction as SqlCeTransaction;
                    command.CommandText = sql;
                    command.Connection = connection;
                    indexName = command.ExecuteScalar() as string;
                }
            }
            finally
            {
                DoneWithConnection(connection, true);
            }
        }

        protected override void CheckOrdinals(string entityName)
        {
            if (Entities[entityName].Fields.OrdinalsAreValid) return;
            var connection = GetConnection(true);
            try
            {
                using (var command = new SqlCeCommand())
                {
                    command.Transaction = CurrentTransaction as SqlCeTransaction;
                    command.Connection = connection as SqlCeConnection;
                    command.CommandText = entityName;
                    command.CommandType = CommandType.TableDirect;
                    using (var reader = command.ExecuteReader())
                    {
                        foreach (var field in Entities[entityName].Fields)
                        {
                            field.Ordinal = reader.GetOrdinal(field.FieldName);
                        }
                        Entities[entityName].Fields.OrdinalsAreValid = true;
                    }
                    command.Dispose();
                }
            }
            finally
            {
                DoneWithConnection(connection, true);
            }
        }
        public int MaxDatabaseSizeInMB
        {
            get { return m_maxSize; }
            set
            {
                // min of 128MB
                if (value < 128) throw new ArgumentOutOfRangeException();
                // max of 4GB
                if (value > 4096) throw new ArgumentOutOfRangeException();
                m_maxSize = value;
            }
        }
        public override string ConnectionString
        {
            get
            {
                if (m_connectionString == null)
                {
                    m_connectionString = string.Format("Data Source={0};Persist Security Info=False;Max Database Size={1};", FileName, MaxDatabaseSizeInMB);
                    if (!string.IsNullOrEmpty(Password))
                    {
                        m_connectionString += string.Format("Password={0};", Password);
                    }
                }
                return m_connectionString;
            }
        }
        protected override IDbConnection GetNewConnectionObject()
        {
            return new SqlCeConnection(ConnectionString);
        }
        protected void ValidateIndex(IDbConnection connection, string indexName, string tableName, string fieldName, bool ascending)
        {
            var valid = false;
            string sql = string.Format("SELECT INDEX_NAME FROM information_schema.indexes WHERE (TABLE_NAME = '{0}') AND (COLUMN_NAME = '{1}')", tableName, fieldName);
            using (SqlCeCommand command = new SqlCeCommand(sql, connection as SqlCeConnection))
            {
                command.Transaction = CurrentTransaction as SqlCeTransaction;
                var name = command.ExecuteScalar() as string;
                if (string.Compare(name, indexName, true) == 0)
                {
                    valid = true;
                }
                if (!valid)
                {
                    sql = string.Format("CREATE INDEX {0} ON {1}({2} {3})",
                        indexName,
                        tableName,
                        fieldName,
                        ascending ? "ASC" : "DESC");
                    command.CommandText = sql;
                    command.ExecuteNonQuery();
                }
            }
        }
        public override string[] GetTableNames()
        {
            var names = new List<string>();
            var connection = GetConnection(true);
            try
            {
                using (var command = GetNewCommandObject())
                {
                    command.Transaction = CurrentTransaction as SqlCeTransaction;
                    command.Connection = connection;
                    var sql = "SELECT table_name FROM information_schema.tables";
                    command.CommandText = sql;
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            names.Add(reader.GetString(0));
                        }
                    }
                    return names.ToArray();
                }
            }
            finally
            {
                DoneWithConnection(connection, true);
            }
        }
        public override bool TableExists(string tableName)
        {
            var connection = GetConnection(true);
            try
            {
                using (var command = GetNewCommandObject())
                {
                    command.Transaction = CurrentTransaction as SqlCeTransaction;
                    command.Connection = connection;
                    var sql = string.Format("SELECT COUNT(*) FROM information_schema.tables WHERE table_name = '{0}'", tableName);
                    command.CommandText = sql;
                    var count = Convert.ToInt32(command.ExecuteScalar());
                    return (count > 0);
                }
            }
            finally
            {
                DoneWithConnection(connection, true);
            }
        }
        public override DynamicEntityDefinition DiscoverDynamicEntity(string entityName)
        {
            if (!TableExists(entityName))
            {
                return null;
            }
            var connection = GetConnection(true);
            try
            {
                using (var cmd = GetNewCommandObject())
                {
                    cmd.Connection = connection;
                    cmd.Transaction = CurrentTransaction;
                    cmd.CommandText = string.Format("SELECT COLUMN_NAME, ORDINAL_POSITION, IS_NULLABLE, DATA_TYPE, NUMERIC_PRECISION, NUMERIC_SCALE FROM information_schema.columns WHERE TABLE_NAME = '{0}' ORDER BY ORDINAL_POSITION", entityName);
                    var fields = new List<FieldAttribute>();
                    using (var reader = cmd.ExecuteReader())
                    {
                        while(reader.Read())
                        {
                            var name = reader.GetString(0);
                            var nullable = string.Compare(reader.GetString(2), "YES", true) == 0;
                            var type = reader.GetString(3).ParseToDbType();
                            var field = new FieldAttribute()
                            {
                                DataType = type,
                                FieldName = name,
                                AllowsNulls = nullable,
                            };
                            if (!reader.IsDBNull(4))
                            {
                                field.Precision = Convert.ToInt32(reader.GetValue(4));
                            }
                            if (!reader.IsDBNull(5))
                            {
                                field.Scale = Convert.ToInt32(reader.GetValue(5));
                            }
                            fields.Add(field);
                        }
                    }
                    cmd.CommandText = string.Format("SELECT COLUMN_NAME, PRIMARY_KEY, [UNIQUE], COLLATION FROM information_schema.indexes WHERE TABLE_NAME = '{0}'", entityName);
                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            var column = reader.GetString(0);
                            var pk = Convert.ToBoolean(reader.GetValue(1));
                            var unique = Convert.ToBoolean(reader.GetValue(2));
                            var field = fields.FirstOrDefault(f => f.FieldName == column);
                            if (pk)
                            {
                                field.IsPrimaryKey = true;
                            }
                            else
                            {
                                var collation = Convert.ToInt32(reader.GetValue(3));
                                field.SearchOrder = collation == 1 ? FieldSearchOrder.Ascending : FieldSearchOrder.Descending; 
                            }
                            if (unique)
                            {
                                field.RequireUniqueValue = true;
                            }
                        }
                    }

                    var entityDefinition = new DynamicEntityDefinition(entityName, fields);
                    RegisterEntityInfo(entityDefinition);
                    return entityDefinition;
                }
            }
            finally
            {
                DoneWithConnection(connection, true);
            }
        }
        protected override void ValidateTable(IDbConnection connection, IEntityInfo entity)
        {
            // prevent caches reads of entitiy fields
            m_lastEntity = null;
            // first make sure the table exists
            if (!TableExists(entity.EntityAttribute.NameInStore))
            {
                CreateTable(connection, entity);
                return;
            }
            using (var command = new SqlCeCommand())
            {
                command.Transaction = CurrentTransaction as SqlCeTransaction;
                command.Connection = connection as SqlCeConnection;
                foreach (var field in entity.Fields)
                {
                    if (ReservedWords.Contains(field.FieldName, StringComparer.InvariantCultureIgnoreCase))
                    {
                        throw new ReservedWordException(field.FieldName);
                    }
                    // yes, I realize hard-coded ordinals are not a good practice, but the SQL isn't changing, it's method specific
                    var sql = string.Format("SELECT column_name, "  // 0
                          + "data_type, "                       // 1
                          + "character_maximum_length, "        // 2
                          + "numeric_precision, "               // 3
                          + "numeric_scale, "                   // 4
                          + "is_nullable "
                          + "FROM information_schema.columns "
                          + "WHERE (table_name = '{0}' AND column_name = '{1}')",
                          entity.EntityAttribute.NameInStore, field.FieldName);
                    command.CommandText = sql;
                    using (var reader = command.ExecuteReader())
                    {
                        if (!reader.Read())
                        {
                            // field doesn't exist - we must create it
                            var alter = new StringBuilder(string.Format("ALTER TABLE {0} ", entity.EntityAttribute.NameInStore));
                            alter.Append(string.Format("ADD [{0}] {1} {2}",
                                field.FieldName,
                                GetFieldDataTypeString(entity.EntityName, field),
                                GetFieldCreationAttributes(entity.EntityAttribute, field)));
                            using (var altercmd = new SqlCeCommand(alter.ToString(), connection as SqlCeConnection))
                            {
                                altercmd.ExecuteNonQuery();
                            }
                        }
                        else
                        {
                            // TODO: verify field length, etc.
                        }
                    }
                }
            }
        }
    }

从客户端(Content="这是测试这...")中检测到有潜在危险的Request.Form 值。

zgc — 2018/8/30 21:59:44

从客户端(Content="这是测试这...")中检测到有潜在危险的Request.Form 值。
说明: 请求验证过程检测到有潜在危险的客户端输入值，对请求的处理已经中止。该值可能指示存在危及应用程序安全的尝试，如跨站点脚本攻击。若要允许页面重写应用程序请求验证设置，请将 httpRuntime 配置节中的 requestValidationMode 特性设置为requestValidationMode="2.0"。示例: 。设置此值后，可通过在 Page 指令或配置节中设置 validateRequest="false" 禁用请求验证。但是，在这种情况下，强烈建议应用程序显式检查所有输入。有关更多信息，请参见http://go.microsoft.com/fwlink/?LinkId=153133。

原以为就像普通的Asp.net页面一样，在头部的Page中加入ValidateRequest="false"就行了，谁知问题还是存在。弄了一个上午，问题终于解决，将解决方法汇总如下：

1,在出现该错误的页面头部的page中加入ValidateRequest="false",那么该页面的任何一次Post提交都不会再验证提交内容的安全性。
如：
<%@ Page Title="" Language="C#" MasterPageFile="~/Views/Manage/ViewMasterPageEdit.Master" Inherits="System.Web.Mvc.ViewPage" ValidateRequest="false" % >
2,在web.config中的pages节中配置validateRequest="false",如：

但这样，整个项目中的所有Form请求都不再验证提交内容的安全性，极不提倡这种做法。

3,如果你使用的是.Net 3.5,MVC 2.0及更高的版本，那么可以在处理Post方法的Action添加一个特性：[ValidateInput(false)]，这样处理就更加有针对性，提高页面的安全性。
如：
[HttpPost] [ValidateInput(false)] public ActionResult CatalogEdit(Catalog model) { return View(); }
重要：
如果你使用的是MVC 3.0，那么你会发现做了以上的设置后还是无效。这是因为你还需要在web.config中做以下设置:

注意：在MVC项目中，Views文件夹下与主项目下，都会有一个web.config文件。Views下的web.config文件只对Views文件夹下面的文件有效。如果你要处理的页面不在Views下面，那么一定要设置在主项目下的web.config中才有用。
转载地址：https://www.cnblogs.com/scgw/p/3602380.html

大师傅大师傅十分时代的

郑国财 — 2018/2/16 12:05:14

sdfldksfldsfldsf
private void jionStr(string str) { retrun str.toString(); }