| 名字 | 描述 |
|---|---|
| character varying(n), varchar(n) | 變長，有長度限制 |
| character(n), char(n) | 定長，不足補空白 |
| text | 變長，無長度限制 |
簡單來說，varchar的長度可變，而char的長度不可變，對于postgresql數(shù)據(jù)庫來說varchar和char的區(qū)別僅僅在于前者是變長，而后者是定長，最大長度都是10485760（1GB）
varchar不指定長度，可以存儲最大長度（1GB）的字符串，而char不指定長度，默認則為1，這點需要注意。
text類型：在postgresql數(shù)據(jù)庫里邊，text和varchar幾乎無性能差別，區(qū)別僅在于存儲結構的不同
對于char的使用，應該在確定字符串長度的情況下使用，否則應該選擇varchar或者text
SQL定義了兩種基本的字符類型：character varying(n) 和character(n)，這里的n 是一個正整數(shù)。兩種類型都可以存儲最多n個字符的字符串（沒有字節(jié)）。試圖存儲更長的字符串到這些類型的字段里會產生一個錯誤，除非超出長度的字符都是空白，這種情況下該字符串將被截斷為最大長度。這個看上去有點怪異的例外是SQL標準要求的。如果要存儲的字符串比聲明的長度短，類型為character的數(shù)值將會用空白填滿；而類型為character varying的數(shù)值將只是存儲短些的字符串。
如果我們明確地把一個數(shù)值轉換成character varying(n) 或character(n)，那么超長的數(shù)值將被截斷成n 個字符，且不會拋出錯誤。這也是SQL標準的要求。
varchar(n)和char(n) 分別是character varying(n) 和character(n)的別名，沒有聲明長度的character等于character(1)；如果不帶長度說明詞使用character varying，那么該類型接受任何長度的字符串。后者是PostgreSQL的擴展。
另外，PostgreSQL提供text類型，它可以存儲任何長度的字符串。盡管類型text不是SQL 標準，但是許多其它SQL數(shù)據(jù)庫系統(tǒng)也有它。
character類型的數(shù)值物理上都用空白填充到指定的長度n，并且以這種方式存儲和顯示。不過，填充的空白是無語意的。在比較兩個character 值的時候，填充的空白都不會被關注，在轉換成其它字符串類型的時候， character值里面的空白會被刪除。請注意，在character varying和text數(shù)值里，結尾的空白是有語意的。并且當使用模式匹配時，如LIKE，使用正則表達式。
一個簡短的字符串（最多126個字節(jié)）的存儲要求是1個字節(jié)加上實際的字符串，其中包括空格填充的character。更長的字符串有4個字節(jié)的開銷，而不是1。長的字符串將會自動被系統(tǒng)壓縮，因此在磁盤上的物理需求可能會更少些。更長的數(shù)值也會存儲在后臺表里面，這樣它們就不會干擾對短字段值的快速訪問。不管怎樣，允許存儲的最長字符串大概是1GB 。允許在數(shù)據(jù)類型聲明中出現(xiàn)的n 的最大值比這還小。修改這個行為沒有什么意義，因為在多字節(jié)編碼下字符和字節(jié)的數(shù)目可能差別很大。如果你想存儲沒有特定上限的長字符串，那么使用text 或沒有長度聲明的character varying，而不要選擇一個任意長度限制。
提示： 這三種類型之間沒有性能差別，除了當使用填充空白類型時的增加存儲空間，和當存儲長度約束的列時一些檢查存入時長度的額外的CPU周期。雖然在某些其它的數(shù)據(jù)庫系統(tǒng)里，character(n) 有一定的性能優(yōu)勢，但在PostgreSQL里沒有。事實上，character(n)通常是這三個中最慢的，因為額外存儲成本。在大多數(shù)情況下，應該使用text 或character varying。
補充：使用PostGreSQL數(shù)據(jù)庫進行text錄入和text檢索
中文分詞
ChineseParse.cs
using System;
using System.Collections;
using System.IO;
using System.Text.RegularExpressions;
namespace FullTextSearch.Common
{
/// summary>
/// 中文分詞器。
/// /summary>
public class ChineseParse
{
private static readonly ChineseWordsHashCountSet _countTable;
static ChineseParse()
{
_countTable = new ChineseWordsHashCountSet();
InitFromFile("ChineseDictionary.txt");
}
/// summary>
/// 從指定的文件中初始化中文詞語字典和字符串次數(shù)字典。
/// /summary>
/// param name="fileName">文件名/param>
private static void InitFromFile(string fileName)
{
string path = Path.Combine(Directory.GetCurrentDirectory(), @"..\..\Common\", fileName);
if (File.Exists(path))
{
using (StreamReader sr = File.OpenText(path))
{
string s = "";
while ((s = sr.ReadLine()) != null)
{
ChineseWordUnit _tempUnit = InitUnit(s);
_countTable.InsertWord(_tempUnit.Word);
}
}
}
}
/// summary>
/// 將一個字符串解析為ChineseWordUnit。
/// /summary>
/// param name="s">字符串/param>
/// returns>解析得到的ChineseWordUnit/returns>
/// 4
/// 0
private static ChineseWordUnit InitUnit(string s)
{
var reg = new Regex(@"\s+");
string[] temp = reg.Split(s);
//if (temp.Length != 2)
//{
// throw new Exception("字符串解析錯誤：" + s);
//}
if (temp.Length != 1)
{
throw new Exception("字符串解析錯誤：" + s);
}
return new ChineseWordUnit(temp[0], Int32.Parse("1"));
}
/// summary>
/// 分析輸入的字符串，將其切割成一個個的詞語。
/// /summary>
/// param name="s">待切割的字符串/param>
/// returns>所切割得到的中文詞語數(shù)組/returns>
public static string[] ParseChinese(string s)
{
int _length = s.Length;
string _temp = String.Empty;
var _words = new ArrayList();
for (int i = 0; i s.Length;)
{
_temp = s.Substring(i, 1);
if (_countTable.GetCount(_temp) > 1)
{
int j = 2;
for (; i + j s.Length + 1 _countTable.GetCount(s.Substring(i, j)) > 0; j++)
{
}
_temp = s.Substring(i, j - 1);
i = i + j - 2;
}
i++;
_words.Add(_temp);
}
var _tempStringArray = new string[_words.Count];
_words.CopyTo(_tempStringArray);
return _tempStringArray;
}
}
}
ChineseWordsHashCountSet.cs
using System.Collections;
namespace FullTextSearch.Common
{
/// summary>
/// 記錄字符串出現(xiàn)在中文字典所錄中文詞語的前端的次數(shù)的字典類。如字符串"中"出現(xiàn)在"中國"的前端，則在字典中記錄一個次數(shù)。
/// /summary>
public class ChineseWordsHashCountSet
{
/// summary>
/// 記錄字符串在中文詞語中出現(xiàn)次數(shù)的Hashtable。鍵為特定的字符串，值為該字符串在中文詞語中出現(xiàn)的次數(shù)。
/// /summary>
private readonly Hashtable _rootTable;
/// summary>
/// 類型初始化。
/// /summary>
public ChineseWordsHashCountSet()
{
_rootTable = new Hashtable();
}
/// summary>
/// 查詢指定字符串出現(xiàn)在中文字典所錄中文詞語的前端的次數(shù)。
/// /summary>
/// param name="s">指定字符串/param>
/// returns>字符串出現(xiàn)在中文字典所錄中文詞語的前端的次數(shù)。若為-1，表示不出現(xiàn)。/returns>
public int GetCount(string s)
{
if (!_rootTable.ContainsKey(s.Length))
{
return -1;
}
var _tempTable = (Hashtable) _rootTable[s.Length];
if (!_tempTable.ContainsKey(s))
{
return -1;
}
return (int) _tempTable[s];
}
/// summary>
/// 向次數(shù)字典中插入一個詞語。解析該詞語，插入次數(shù)字典。
/// /summary>
/// param name="s">所處理的字符串。/param>
public void InsertWord(string s)
{
for (int i = 0; i s.Length; i++)
{
string _s = s.Substring(0, i + 1);
InsertSubString(_s);
}
}
/// summary>
/// 向次數(shù)字典中插入一個字符串的次數(shù)記錄。
/// /summary>
/// param name="s">所插入的字符串。/param>
private void InsertSubString(string s)
{
if (!_rootTable.ContainsKey(s.Length) s.Length > 0)
{
var _newHashtable = new Hashtable();
_rootTable.Add(s.Length, _newHashtable);
}
var _tempTable = (Hashtable) _rootTable[s.Length];
if (!_tempTable.ContainsKey(s))
{
_tempTable.Add(s, 1);
}
else
{
_tempTable[s] = (int) _tempTable[s] + 1;
}
}
}
}
ChineseWordUnit.cs
namespace FullTextSearch.Common
{
public struct ChineseWordUnit
{
private readonly int _power;
private readonly string _word;
/// summary>
/// 結構初始化。
/// /summary>
/// param name="word">中文詞語/param>
/// param name="power">該詞語的權重/param>
public ChineseWordUnit(string word, int power)
{
_word = word;
_power = power;
}
/// summary>
/// 中文詞語單元所對應的中文詞。
/// /summary>
public string Word
{
get { return _word; }
}
/// summary>
/// 該中文詞語的權重。
/// /summary>
public int Power
{
get { return _power; }
}
}
}
ChineseDictionary.txt
主窗體界面
MainManager.cs
using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using FullTextSearch.Common;
using Npgsql;
namespace FullTextSearch
{
public partial class MainManager : Form
{
private readonly PostgreSQL pg = new PostgreSQL();
private readonly SQLquerys sqlQuerys = new SQLquerys();
private char analysisType;
private string createConnString = "";
private DataSet dataSet = new DataSet();
private DataTable dataTable = new DataTable();
private char odabirAndOr;
private char vrstaPretrazivanja;
public MainManager()
{
InitializeComponent();
rbtn_AND.Checked = true;
rbtnNeizmjenjeni.Checked = true;
odabirAndOr = '*';
radioButton_Day.Checked = true;
radioButton_Day.Checked = true;
}
private void Form1_Load(object sender, EventArgs e)
{
gb_unosPodataka.Enabled = false;
groupBox_Search.Enabled = false;
groupBox_Analysis.Enabled = false;
button_Disconnect.Enabled = false;
button_Pretrazi.BackColor = Color.WhiteSmoke;
button_Disconnect.BackColor = Color.WhiteSmoke;
button_unosTekstaUBazu.BackColor = Color.WhiteSmoke;
button1.BackColor = Color.WhiteSmoke;
}
private void button_unosTekstaUBazu_Click(object sender, EventArgs e)
{
string searchTextBoxString = rTB_unosTextaUBazu.Text;
if (searchTextBoxString != "")
{
pg.insertIntoTable(searchTextBoxString, pg.conn);
MessageBox.Show(searchTextBoxString + " 添加到數(shù)據(jù)庫!");
rTB_unosTextaUBazu.Clear();
}
else
{
MessageBox.Show("不允許空數(shù)據(jù)!");
}
}
private void button_Pretrazi_Click(object sender, EventArgs e)
{
string stringToSearch;
string sql;
string highlitedText;
string rank;
string check;
stringToSearch = txt_Search.Text.Trim();
var list = new Liststring>(ChineseParse.ParseChinese(stringToSearch));
;
sql = sqlQuerys.createSqlString(list, odabirAndOr, vrstaPretrazivanja);
richTextBox1.Text = sql;
check = sqlQuerys.testIfEmpty(stringToSearch);
pg.insertIntoAnalysisTable(stringToSearch, pg.conn);
pg.openConnection();
var command = new NpgsqlCommand(sql, pg.conn);
NpgsqlDataReader reader = command.ExecuteReader();
int count = 0;
linkLabel_Rezultat.Text = " ";
while (reader.Read())
{
highlitedText = reader[1].ToString();
rank = reader[3].ToString();
linkLabel_Rezultat.Text += highlitedText + "[" + rank + "]\n";
count++;
}
labelBrojac.Text = "找到的文件數(shù)量: " + count;
pg.closeConnection();
}
private void rbtn_AND_CheckedChanged(object sender, EventArgs e)
{
odabirAndOr = '*';
}
private void rbtn_OR_CheckedChanged(object sender, EventArgs e)
{
odabirAndOr = '+';
}
private void rbtnNeizmjenjeni_CheckedChanged(object sender, EventArgs e)
{
vrstaPretrazivanja = 'A';
}
private void rbtn_Rijecnici_CheckedChanged(object sender, EventArgs e)
{
vrstaPretrazivanja = 'B';
}
private void rbtn_Fuzzy_CheckedChanged(object sender, EventArgs e)
{
vrstaPretrazivanja = 'C';
}
private void button_Connect_Click(object sender, EventArgs e)
{
if (connectMe())
{
gb_unosPodataka.Enabled = true;
groupBox_Search.Enabled = true;
groupBox_Analysis.Enabled = true;
textBox_Database.Enabled = false;
textBox_IP.Enabled = false;
textBox_Port.Enabled = false;
textBox_Password.Enabled = false;
textBox_UserID.Enabled = false;
button_Connect.Enabled = false;
button_Disconnect.Enabled = true;
button_Pretrazi.BackColor = Color.SkyBlue;
button_Disconnect.BackColor = Color.IndianRed;
button_unosTekstaUBazu.BackColor = Color.MediumSeaGreen;
button1.BackColor = Color.MediumSeaGreen;
button_Connect.BackColor = Color.WhiteSmoke;
}
}
private void button_Disconnect_Click(object sender, EventArgs e)
{
gb_unosPodataka.Enabled = false;
groupBox_Search.Enabled = false;
groupBox_Analysis.Enabled = false;
textBox_Database.Enabled = true;
textBox_IP.Enabled = true;
textBox_Port.Enabled = true;
textBox_Password.Enabled = true;
textBox_UserID.Enabled = true;
button_Connect.Enabled = true;
button_Disconnect.Enabled = false;
button_Pretrazi.BackColor = Color.WhiteSmoke;
button_Disconnect.BackColor = Color.WhiteSmoke;
button_unosTekstaUBazu.BackColor = Color.WhiteSmoke;
button1.BackColor = Color.WhiteSmoke;
button_Connect.BackColor = Color.MediumSeaGreen;
txt_Search.Text = "";
linkLabel_Rezultat.Text = "";
richTextBox1.Text = "";
labelBrojac.Text = "";
}
private bool connectMe()
{
createConnString += "Server=" + textBox_IP.Text + ";Port=" + textBox_Port.Text + ";User Id=" +
textBox_UserID.Text + ";Password=" + textBox_Password.Text + ";Database=" +
textBox_Database.Text + ";";
sqlQuerys.setTheKey(createConnString);
pg.setConnectionString();
pg.setConnection();
if (pg.openConnection())
{
MessageBox.Show("您已成功連接!");
pg.closeConnection();
return true;
}
return false;
}
private void button1_Click(object sender, EventArgs e)
{
string selectedTimest
selectedTimestamp = dateTimePicker_From.Value.ToString("dd-MM-yyyy hh:mm:ss") + " " +
dateTimePicker_To.Value.ToString("dd-MM-yyyy hh:mm:ss");
var analize = new Analysis(selectedTimestamp, analysisType);
analize.Show();
}
private void radioButton_Day_CheckedChanged(object sender, EventArgs e)
{
analysisType = 'D';
}
private void radioButton_Hour_CheckedChanged(object sender, EventArgs e)
{
analysisType = 'H';
}
}
}
SQLquerys.cs代碼:
using System.Collections.Generic;
namespace FullTextSearch
{
internal class SQLquerys
{
private static string giveMeTheKey;
private static int tempInt = 1;
//設置連接字符串
public void setTheKey(string connString)
{
giveMeTheKey = connString;
giveMeTheKey += "";
}
//將連接字符串存儲在靜態(tài)變量中
public string getTheKey()
{
giveMeTheKey += "";
return giveMeTheKey;
}
public void setCounter()
{
tempInt = 1;
}
//根據(jù)AND和OR的選擇分析字符串進行搜索
public string createFunctionString(Liststring> searchList, char selector)
{
string TempString = "";
string[] TempField = null;
int i = 0;
int j = 0;
foreach (string searchStringInList in searchList)
{
if (j != 0)
{
if (selector == '+')
TempString = TempString + " | ";
else if (selector == '*')
TempString = TempString + " ";
}
j = 1;
TempField = splitListForInput(searchStringInList);
TempString = TempString + "(";
foreach (string justTempString in TempField)
{
if (i != 0)
{
TempString = TempString + " ";
}
TempString = TempString + justTempString;
i = 1;
}
TempString = TempString + ")";
i = 0;
}
return TempString;
}
//幫助方法
public Liststring> splitInputField(string[] inputField)
{
var unfinishedList = new Liststring>();
foreach (string splitString in inputField)
{
unfinishedList.Add(splitString);
}
return unfinishedList;
}
//幫助方法
public string[] splitListForInput(string inputString)
{
string[] parsedList = null;
parsedList = inputString.Split(' ');
return parsedList;
}
//在PostgreSQL中創(chuàng)建ts功能的功能，用于字典搜索
public string createTsFunction(string tsString)
{
string tsHeadline = "";
string tsRank = "";
string tsFunction = "";
tsHeadline = ",\n ts_headline(\"content\", to_tsquery('" + tsString + "')), \"content\"";
tsRank = ",\n ts_rank(to_tsvector(\"content\"), to_tsquery('" + tsString + "')) rank";
tsFunction = tsHeadline + tsRank;
return tsFunction;
}
//創(chuàng)建SQL查詢依賴于選擇哪種類型的搜索，也取決于AND或OR選擇器
public string createSqlString(Liststring> searchList, char selector, char vrstaPretrazivanja)
{
string selectString = "";
string myTempString = "";
string TempString = "";
int i = 0;
TempString = createFunctionString(searchList, selector);
TempString = createTsFunction(TempString);
selectString = "SELECT \"id\"" + TempString + "\nFROM \"texttable\" \nWHERE ";
if (vrstaPretrazivanja == 'A')
{
foreach (string myString in searchList)
{
if (i == 0)
{
myTempString = myTempString + "\"content\" LIKE '%" + myString + "%' ";
i++;
}
else
{
if (selector == '*')
myTempString = myTempString + "\nAND \"content\" LIKE '%" + myString + "%' ";
else if (selector == '+')
myTempString = myTempString + "\nOR \"content\" LIKE '%" + myString + "%' ";
}
}
}
else if (vrstaPretrazivanja == 'B')
{
foreach (string myString in searchList)
{
string temporalString = "";
string[] testingString = myString.Split(' ');
for (int k = 0; k testingString.Length; k++)
{
if (k != testingString.Length - 1)
{
temporalString += testingString[k] + " ";
}
else
{
temporalString += testingString[k];
}
}
if (i == 0)
{
myTempString = myTempString + "to_tsvector(\"content\") @@ to_tsquery('english', '" +
temporalString + "')";
i++;
}
else
{
if (selector == '*')
myTempString = myTempString + "\nAND to_tsvector(\"content\") @@ to_tsquery('english', '" +
temporalString + "')";
else if (selector == '+')
myTempString = myTempString + "\nOR to_tsvector(\"content\") @@ to_tsquery('english', '" +
temporalString + "')";
}
}
}
if (vrstaPretrazivanja == 'C')
{
foreach (string myString in searchList)
{
if (i == 0)
{
myTempString = myTempString + "\"content\" % '" + myString + "' ";
i++;
}
else
{
if (selector == '*')
myTempString = myTempString + "\nAND \"content\" % '" + myString + "' ";
else if (selector == '+')
myTempString = myTempString + "\nOR \"content\" % '" + myString + "' ";
}
}
}
selectString = selectString + myTempString + "\nORDER BY rank DESC";
return selectString;
}
public string testIfEmpty(string searchedText)
{
string checkingIfEmpty = "SELECT * FROM \"analysisTable\" WHERE \"searchedtext\" =' " + searchedText + "'";
return checkingIfEmpty;
}
public string queryForAnalysis(char analysisChoice)
{
string myTestsql = "";
if (analysisChoice == 'H')
{
//這個查詢是這樣寫的只是為了測試的目的，它需要改變
myTestsql = "SELECT * FROM crosstab('SELECT CAST((\"searchedtext\") AS text) searchedText,"
+
" CAST(EXTRACT(HOUR FROM \"timeOfSearch\") AS int) AS sat, CAST(COUNT(*) AS int) AS broj FROM \"analysisTable\" GROUP BY \"searchedText\", sat"
+
" ORDER BY \"searchedtext\", sat', 'SELECT rbrSata FROM sat ORDER BY rbrSata') AS pivotTable (\"searchedText\" TEXT, t0_1 INT, t1_2 INT"
+
", t2_3 INT, t3_4 INT, t4_5 INT, t5_6 INT, t6_7 INT, t7_8 INT, t8_9 INT, t9_10 INT, t10_11 INT, t11_12 INT, t12_13 INT"
+
", t13_14 INT, t14_15 INT, t15_16 INT, t16_17 INT, t17_18 INT, t18_19 INT, t19_20 INT, t20_21 INT, t21_22 INT, t22_23 INT, t23_00 INT) ORDER BY \"searchedText\"";
return myTestsql;
}
if (analysisChoice == 'D')
{
//這個查詢是這樣寫的只是為了測試的目的，它需要改變
myTestsql += "SELECT *FROM crosstab ('SELECT CAST((\"searchedtext\") AS text) AS searchedText, CAST(EXTRACT(DAY FROM \"dateOfSearch\") AS int) AS dan"
+ ", CAST(COUNT(*) AS int) AS broj FROM \"analysisTable\" GROUP BY \"searchedText\", "
+
"dan ORDER BY \"searchedtext\", dan', 'SELECT rbrDana FROM dan ORDER BY rbrDana') AS pivotTable(\"searchedtext\" TEXT";
return myTestsql;
}
return myTestsql;
}
//此方法用于解析日期
public int[] parseForDates(string date)
{
string[] temp;
var tempInt = new int[3];
temp = date.Split('-');
for (int i = 0; i 3; i++)
{
tempInt[i] = int.Parse(temp[i]);
}
return tempInt;
}
//此代碼用于創(chuàng)建分析，它執(zhí)行一些日期/時間操作，以便能夠為選定的日期/時間創(chuàng)建分析。
public string createSqlForDayAnalysis(string dateFrom, string dateTo)
{
string insertIntoTempTable = "";
string dateTimeForAnalysis = "";
int[] tempFrom = parseForDates(dateFrom);
int[] tempTo = parseForDates(dateTo);
//月份變更算法
while (tempFrom[0] != tempTo[0] || tempFrom[1] != tempTo[1])
{
if (tempFrom[1] == tempTo[1])
{
if (tempFrom[0] != tempTo[0])
{
for (int i = tempInt + 1; tempFrom[0] + 2 tempTo[0] + 2; i++)
{
insertIntoTempTable += "INSERT INTO \"dan\" VALUES (" + i + ");";
dateTimeForAnalysis += ",dd" + tempFrom[0] + tempFrom[1] + tempFrom[2] + " INT";
tempInt = i;
tempFrom[0]++;
}
}
}
if (tempFrom[1] != tempTo[1])
{
if (tempFrom[1]%2 == 0 || tempFrom[1] == 7 || tempFrom[1] == 1)
{
for (int i = tempInt; tempFrom[0] 31 tempFrom[1] != tempTo[1]; i++)
{
insertIntoTempTable += "INSERT INTO \"dan\" VALUES (" + i + ");";
dateTimeForAnalysis += ", dd" + tempFrom[0] + tempFrom[1] + tempFrom[2] + " INT";
tempInt = i;
tempFrom[0]++;
if (tempFrom[0] == 31)
{
tempFrom[1]++;
tempFrom[0] = 1;
}
}
}
}
}
dateTimeForAnalysis += ") ORDER BY \"searchedtext\"";
return dateTimeForAnalysis + "#" + insertIntoTempTable;
}
}
}
PostgreSQL.cs代碼:
using System;
using System.Windows.Forms;
using Npgsql;
using NpgsqlTypes;
namespace FullTextSearch
{
public class PostgreSQL
{
private static int tempInt = 1;
private readonly SQLquerys sql = new SQLquerys();
public NpgsqlConnection conn;
public string connectionstring;
private string newConnString;
public PostgreSQL()
{
setConnectionString();
setConnection();
}
public void setConnectionString()
{
newConnString = sql.getTheKey();
connectionstring = String.Format(newConnString);
setConnection();
}
public void setConnection()
{
conn = new NpgsqlConnection(connectionstring);
}
public bool openConnection()
{
try
{
conn.Open();
return true;
}
catch
{
MessageBox.Show("Unable to connect! Check parameters!");
return false;
}
}
public void closeConnection()
{
conn.Close();
}
public void insertIntoTable(string textToInsert, NpgsqlConnection nsqlConn)
{
string mySqlString = "INSERT INTO \"texttable\" (\"content\") VALUES (@Param1)";
var myParameter = new NpgsqlParameter("@Param1", NpgsqlDbType.Text);
myParameter.Value = textToInsert;
openConnection();
var myCommand = new NpgsqlCommand(mySqlString, nsqlConn);
myCommand.Parameters.Add(myParameter);
myCommand.ExecuteNonQuery();
closeConnection();
}
public void insertIntoAnalysisTable(string textToInsert, NpgsqlConnection nsqlConn)
{
string dateTime = DateTime.Now.ToString();
string[] temp;
temp = dateTime.Split(' ');
string mySqlString =
"INSERT INTO \"analysistable\" (\"searchedtext\", \"dateofsearch\", \"timeofsearch\") VALUES ('" +
textToInsert + "', '" + temp[0] + "'" + ", '" + temp[1] + "');";
openConnection();
var myCommand = new NpgsqlCommand(mySqlString, nsqlConn);
myCommand.ExecuteNonQuery();
closeConnection();
}
public void executeQuery(string queryText, NpgsqlConnection nsqlConn)
{
openConnection();
var myCommand = new NpgsqlCommand(queryText, nsqlConn);
myCommand.ExecuteNonQuery();
closeConnection();
}
public void createTempTable(NpgsqlConnection nsqlConn, char analysisType, string dateFrom, string dateTo,
string splitMe)
{
if (analysisType == 'H')
{
string dropIfExists = "DROP TABLE IF EXISTS \"sat\";";
string createTempTable = "CREATE TABLE IF NOT EXISTS \"sat\" (rbrSata INT);";
string insertIntoTempTable = "";
for (int i = 0; i 24; i++)
{
insertIntoTempTable += "INSERT INTO \"sat\" VALUES (" + i + ");";
}
openConnection();
var commandDrop = new NpgsqlCommand(dropIfExists, nsqlConn);
commandDrop.ExecuteNonQuery();
var commandCreate = new NpgsqlCommand(createTempTable, nsqlConn);
commandCreate.ExecuteNonQuery();
var commandInsert = new NpgsqlCommand(insertIntoTempTable, nsqlConn);
commandInsert.ExecuteNonQuery();
closeConnection();
}
else if (analysisType == 'D')
{
string dropIfExists = "DROP TABLE IF EXISTS \"dan\";";
string createTempTable = "CREATE TABLE IF NOT EXISTS \"dan\" (rbrDana INT);";
string insertIntoTempTable = splitMe;
openConnection();
var commandDrop = new NpgsqlCommand(dropIfExists, nsqlConn);
commandDrop.ExecuteNonQuery();
var commandCreate = new NpgsqlCommand(createTempTable, nsqlConn);
commandCreate.ExecuteNonQuery();
var commandInsert = new NpgsqlCommand(insertIntoTempTable, nsqlConn);
commandInsert.ExecuteNonQuery();
closeConnection();
}
}
}
}
PostGreSQL sql腳本:
CREATE TABLE public.analysistable
(
id integer NOT NULL DEFAULT nextval('analysistable_id_seq'::regclass),
searchedtext text COLLATE pg_catalog."default" NOT NULL,
dateofsearch date NOT NULL,
timeofsearch time without time zone NOT NULL,
CONSTRAINT analysistable_pkey PRIMARY KEY (id)
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;
ALTER TABLE public.analysistable
OWNER to king;
CREATE TABLE public.texttable
(
id integer NOT NULL DEFAULT nextval('texttable_id_seq'::regclass),
content text COLLATE pg_catalog."default" NOT NULL,
CONSTRAINT texttable_pkey PRIMARY KEY (id)
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;
ALTER TABLE public.texttable
OWNER to king;
運行結果如圖:
以上為個人經驗，希望能給大家一個參考，也希望大家多多支持腳本之家。如有錯誤或未考慮完全的地方，望不吝賜教。
